Convert Rows to column

  • 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#NamePercent
    100John25
    100Harry50
    100Smith25
    101Rob100
    102Chris100
    103Pat75
    103Chen25

    Into following result set

    Tr#Name1Name2Name3 
    100HarryJohnSmith
    101Rob
    102Chris
    103PatChen

    Any ideas!! Shoot fast

  • This thread might give some idea for you to get started

     

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=166709


    Regards,

    Meghana

  • 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

  • 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

     

  • 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