SQL Server- PIVOT table. transform row into columns

Question

I am trying to convert rows into columns. here is my query

SELECT M.urn,
       M.eventdate,
       M.eventlocation,
       M.eventroom,
       M.eventbed,
       N.time
FROM   admpatevents M
       INNER JOIN admpattransferindex N
               ON M.urn = N.urn
                  AND M.eventseqno = N.eventseqno
                  AND M.eventdate = N.eventdate
WHERE  M.urn = 'F1002754364'
       AND M.eventcode = 'TFRADMIN'

Current result

URN           Date      Location    Room    Bed  Time
F1002754364 20121101    EDEXPRESS   4-152   02      0724
F1002754364 20121101    CARDSURG    3-110   02      1455
F1002754364 20121102    CHEST UNIT  6-129-GL04      1757

required result

F1002754364    20121101    EDEXPRESS   4-152   02  0724 20121101   CARDSURG    3-110   02  1455 20121102   CHEST UNIT  6-129-GL    04  1757

Thanks for your help.

Answer 1

Since you are using SQL Server you can use both the UNPIVOT and PIVOT functions to transform this data. If you know how many values to you will have then you can hard-code the values similar to this:

select *
from
(
    select urn,
        value,
        col +'_'+ CAST(rn as varchar(10)) as col
    from 
    (
        SELECT M.urn,
            cast(M.eventdate as varchar(50)) eventdate,
            M.eventlocation,
            M.eventroom,
            M.eventbed,
            cast(N.time as varchar(50)) time,
            ROW_NUMBER() over(PARTITION by m.urn order by m.eventdate) rn
        FROM   admpatevents M
        INNER JOIN admpattransferindex N
            ON M.urn = N.urn
            AND M.eventseqno = N.eventseqno
            AND M.eventdate = N.eventdate
        WHERE  M.urn = 'F1002754364'
            AND M.eventcode = 'TFRADMIN'
    ) src1
    unpivot
    (
        value 
        for col in (eventdate, eventlocation, eventroom, eventbed, time)
    ) unpiv
) src2
pivot
(
    max(value)
    for col in ([eventdate_1], [eventlocation_1], [eventroom_1], [eventbed_1], [time_1],
                [eventdate_2], [eventlocation_2], [eventroom_2], [eventbed_2], [time_2],
                [eventdate_3], [eventlocation_3], [eventroom_3], [eventbed_3], [time_3])
) piv

Note- not tested

If you have an unknown number of columns, then you can use dynamic sql similar to this:

DECLARE  @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX)

select @colsPivot = STUFF((SELECT  ',' 
                      + quotename(c.name +'_'+ cast(t.rn as varchar(10)))
                    from
                    (
                        select ROW_NUMBER() over(PARTITION by m.urn order by m.eventdate) rn
                        FROM   admpatevents M
                        INNER JOIN admpattransferindex N
                            ON M.urn = N.urn
                            AND M.eventseqno = N.eventseqno
                            AND M.eventdate = N.eventdate
                        WHERE  M.urn = 'F1002754364'

                    ) t
                    cross apply sys.columns as C
                   where C.object_id IN (object_id('admpatevents'), object_id('admpattransferindex')) and
                         C.name not in ('urn') -- add any other columns your want to exclude
                   group by c.name, t.rn
                   order by t.rn
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
  = 'select *
     from
     (
       select urn, value, col +''_''+ CAST(rn as varchar(10)) as col
       from
       (
            SELECT M.urn,
                cast(M.eventdate as varchar(20)) eventdate,
                M.eventlocation,
                M.eventroom,
                M.eventbed,
                cast(N.time as varchar(20)) time,
                ROW_NUMBER() over(PARTITION by m.urn order by m.eventdate) rn
            FROM   admpatevents M
            INNER JOIN admpattransferindex N
                ON M.urn = N.urn
                AND M.eventseqno = N.EVENTseqno
                AND M.eventdate = N.eventdate
            WHERE  M.urn = ''F1002754364''

       ) x
       unpivot
       (
         value
         for col in (eventdate, eventlocation, eventroom, eventbed, time)
       ) u
      ) x1
      pivot
      (
        max(value)
        for col in ('+ @colspivot +')
      ) p'

exec(@query)

See SQL Fiddle with Demo