根据条件sql将行转换为列

小编典典

根据条件sql将行转换为列

sql

我有一个下表（一个简化的示例，实际上该表包含多个ID，具有可变数量的日期和可变数量的每个日期的事件）：

IDs   Date                  Event
102   1996-10-16 00:00:00   A
102   1996-10-23 00:00:00   A
102   1996-10-23 00:00:00   B
102   1997-01-14 00:00:00   A
103   1997-01-14 00:00:00   D
103   1997-01-15 00:00:00   A
103   1997-01-16 00:00:00   A
103   1997-01-16 00:00:00   B
103   1997-01-16 00:00:00   C

我正在尝试获得一个表，在该表中我将具有不同的ID /日期对，并将针对多个事件进行重新编码的行转置为列。因此，我正在寻找一个表，对于本示例，该表将如下所示：

IDs   Date                  Event   Event2   Event3
102   1996-10-16 00:00:00   A       NULL     NULL
102   1996-10-23 00:00:00   A       B        NULL
102   1997-01-14 00:00:00   A       NULL     NULL
103   1997-01-14 00:00:00   D       NULL     NULL
103   1997-01-15 00:00:00   A       NULL     NULL
103   1997-01-16 00:00:00   A       B        C

很抱歉没有发布任何代码，但是坦率地说，我什至不知道如何开始。

阅读 129

2021-05-16

共1个答案

小编典典

有关PIVOT方法的详细信息。

试试下面的代码：

-- Temporary table...
create table ##myTable (
        IDs int
        ,[Date] datetime
        ,[Event] varchar(1)
        )

-- ... with sample data
insert ##myTable
        select 102, '2010-01-01', 'A'
union   select 102, '2010-01-01', 'B'
union   select 102, '2010-01-01', 'C'
union   select 102, '2010-01-01', 'E'
union   select 103, '2010-01-01', 'A'
union   select 104, '2010-01-01', 'B'
union   select 104, '2010-01-01', 'C'
union   select 105, '2010-01-01', 'F'

-- Variables
DECLARE @cols   AS NVARCHAR(MAX)
        ,@query AS NVARCHAR(MAX)

-- Build column name for our result.
-- The ROW_NUMBER() operator gives us the rank of the event for
-- the combination of IDs and Date. With that, event B for IDs 104
-- will have rank 1, and then will appear in the 1st column.
SELECT  @cols = STUFF(
                       (SELECT  DISTINCT
                        ',' + QUOTENAME('Event' + LTRIM(STR(
                                    ROW_NUMBER() OVER (
                                        PARTITION BY IDs, [Date]
                                        ORDER BY IDs, [Date]
                                    )
                                )))
                        FROM    ##myTable
                        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
                    , 1, 1, '')

set @query = '
    SELECT  IDs, [Date], ' + @cols + '
    FROM    (
            SELECT  IDs
                    ,[Date]
                    ,[Event]
                    ,''Event'' + LTRIM(STR(
                            ROW_NUMBER() OVER (
                                PARTITION BY IDs, [Date]
                                ORDER BY IDs, [Date]
                            )
                    )) as [EventNo]
            FROM    ##myTable
            ) x
    PIVOT
            (
            MAX([Event])
            FOR [EventNo] IN (' + @cols + ')
            ) p'

execute sp_executesql @query

-- Remove temporary table
drop table ##myTable

2021-05-16