根据条件sql将行转换成列 [英] Transposing rows into columns based on a condition sql

问题描述

我有一个下表（一个简化的例子，其实表中包含多个ID，可变数量的日期，每个日期的可变事件数）：

I have a following table (a simplified example, in fact the table contains multiple IDs, with variable numbers of dates, and variable number of events for each date):

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 /日期对，其中已经重新编码的多个事件的行被转换成列。所以，我正在寻找一个表，这个例子看起来像这样：

I am trying to get a table where I will have distinct IDs/Date pairs, with the rows for which there have been multiple events recoded being transposed into columns. So, I'm looking for a table which for this example would look like this:

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

对于没有发布任何代码，我很抱歉，但我坦白说甚至不知道

I'm sorry for not posting any code, but I frankly don't even know how to start with this.

推荐答案

有关 PIVOT 方法。

和helpfull答案：

And helpfull answers:

在SQL Server 2008中使用PIVOT

MSSQL动态枢轴列值到列标题

尝试此代码：

-- 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

结果：

这篇关于根据条件sql将行转换成列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！