SQL Server-PIVOT表.将行转换为列 [英] SQL Server- PIVOT table. transform row into columns

问题描述

我正在尝试将行转换为列.这是我的查询

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'

当前结果

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

需要的结果

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

感谢您的帮助.

推荐答案

由于使用的是SQL Server，因此可以同时使用UNPIVOT和PIVOT函数来转换此数据.如果您知道将要包含多少个值，则可以使用以下方式对值进行硬编码:

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

注意-未经测试

如果列数未知，则可以使用类似于以下内容的动态sql:

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)

请参见带有演示的SQL提琴

这篇关于SQL Server-PIVOT表.将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！