0

I have table as following

   ID   FName    LName
   r1   Tom      Patrik
   r2   Jerry    Blaku
   r1   Ethan    Lie

I would like something as following 

   ID      r1       r2      r1
   FName   Tom      Jerry   Ethan
   LName   Patrik   Blaku   Lie

NOTE THAT VALUES IN ID ARE NOT DISTINCT....!! Is it possible to achieve this using sql Server Pivot(Or any) command, If yes I will really appreciate TSQL for that

A P
  • 198
  • 2
  • 8
  • (duplicate) It's called a dynamic pivot. Take a look. http://stackoverflow.com/questions/8327261/dynamic-sql-pivot-in-sql-server http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx – Sam DeHaan Mar 19 '12 at 19:26

1 Answers1

2

I am sure there are better ways of doing this. But since you are trying to pivot multiple columns here is an ugly solution:

create table #temp
(
    id int,
    fname varchar(50),
    lname varchar(50)
)

insert into #temp values(1, 'Tom', 'Patrik')
insert into #temp values(2, 'Jerry', 'Blaku')

SELECT 'fname', P.Tom as '1', P.Jerry as '2'
FROM
(
    SELECT fname
    FROM #temp
) I
PIVOT
(
    min(fname)
    FOR [fname] IN ([Tom], [Jerry])
) as P
UNION
SELECT 'lname', P.Patrik as '1', P.Blaku as '2'
FROM
(
    SELECT lname
    FROM #temp
) I
PIVOT
(
    min(lname)
    FOR [lname] IN ([Patrik], [Blaku])
) as P

drop table #temp
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • Thanks for your answer. I actually have very complex query and I can not write pivot for each row. I mentioned very easy table just to get idea of how to do this using Pivot. – A P Mar 19 '12 at 20:06
  • You can try using a dynamic sql pivot. – Taryn Mar 19 '12 at 20:10