September 15, 2005 at 9:32 pm
Here is a tricky one!
For a requirement of Selecting two names for each distinct transaction with highest percentage
I need to transpose the following data set using T-SQL.
| Tr# | Name | Percent | |
| 100 | John | 25 | |
| 100 | Harry | 50 | |
| 100 | Smith | 25 | |
| 101 | Rob | 100 | |
| 102 | Chris | 100 | |
| 103 | Pat | 75 | |
| 103 | Chen | 25 |
Into following result set
| Tr# | Name1 | Name2 | Name3 | ||
| 100 | Harry | John | Smith | ||
| 101 | Rob | ||||
| 102 | Chris | ||||
| 103 | Pat | Chen |
Any ideas!! Shoot fast
September 16, 2005 at 1:35 am
This thread might give some idea for you to get started
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=166709
September 16, 2005 at 2:51 am
This suggestion requires that the rows of your table are unique:
create table testtable(#Tr int, Name varchar(100), Perc int)
go
insert into testtable select 100, 'John', 25
insert into testtable select 100, 'Harry', 50
insert into testtable select 100, 'Smith', 25
insert into testtable select 101, 'Rob', 100
insert into testtable select 102, 'Chris', 100
insert into testtable select 103, 'Pat', 75
insert into testtable select 103, 'Chen', 25
select t1.#Tr, t1.Name, t2.Name, t3.Name from testtable t1
left join
testtable t2
on
t1.#Tr = t2.#Tr
and
t2.Name in (select top 2 name from testtable where #Tr = t1.#Tr order by perc desc, Name)
and
t2.Name not in (select top 1 name from testtable where #Tr = t1.#Tr order by perc desc, Name)
left join testtable t3
on
t1.#Tr = t3.#Tr
and
t3.Name in (select top 3 name from testtable where #Tr = t1.#Tr order by perc desc, Name)
and
t3.Name not in (select top 2 name from testtable where #Tr = t1.#Tr order by perc desc, Name)
where
t1.Name = (select top 1 name from testtable where #Tr = t1.#Tr order by perc desc, Name)
order by t1.#Tr
September 16, 2005 at 12:19 pm
It returns correct result set but there are performance issues if this is used on big table.
Any other Ideas which provide better performance with desired result.
Thanks
GOpal
September 18, 2005 at 2:00 pm
I have posted a different solution below, which creates and uses a second table named testtable2. Please let me know if this performs better.
create table testtable(#Tr int, Name varchar(100), Perc int)
go
insert into testtable select 100, 'John', 25
insert into testtable select 100, 'Harry', 50
insert into testtable select 100, 'Smith', 25
insert into testtable select 101, 'Rob', 100
insert into testtable select 102, 'Chris', 100
insert into testtable select 103, 'Pat', 75
insert into testtable select 103, 'Chen', 25
create table testtable2(id int identity(1,1), #Tr int, Name varchar(100), Perc int)
go
create clustered index testtable2index on dbo.testtable2(#Tr, id)
go
insert into testtable2 (#Tr, Name, Perc) select #Tr, Name, Perc from testtable
order by #Tr, Perc desc, Name
select t1.#Tr, t1.Name, t2.Name, t3.Name from
testtable2 t1 inner join
(select #Tr, min(id) as minid from testtable2 group by #Tr) dt
on t1.#Tr = dt.#Tr
left join testtable2 t2
on t2.#Tr = t1.#Tr and t2.id = t1.id + 1
left join testtable2 t3
on t3.#Tr = t1.#Tr and t3.id = t1.id + 2
where t1.id = dt.minid
order by t1.#Tr
drop table testtable2
go
drop table testtable
go
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply