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.

Taryn · Accepted Answer · 2012-12-04 19:52:15Z

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

edited Dec 4, 2012 at 19:52

answered Dec 4, 2012 at 17:10

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matt Over a year ago

thanks for the help.I am getting error when I ran both statements. First statement error:The type of column "eventlocation" conflicts with the type of other columns specified in the UNPIVOT list. Second Statement error: Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'SELECT'. Msg 102, Level 15, State 1, Line 21 Incorrect syntax near ')'. Thanks again

Taryn Over a year ago

@Matt The datatypes need to be the same on all columns being unpivoted. So you will have to perform the conversion similar to what I did for the eventdate. If you want, please create a sql fiddle with your tables and some sample data so I can test. :)

Matt Over a year ago

First statement worked after changing the datatype as you mentioned. second statement, has some minor issues like ')' . I tried to change ')' around but didn't work. let me create schema in SQL fiddle. I'll let you know. Thanks

Taryn Over a year ago

@Matt see my edit, it is working now on this demo -- sqlfiddle.com/#!3/6c1cd/16

Collectives™ on Stack Overflow

SQL Server- PIVOT table. transform row into columns

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related