动态转换行列SQL(Dynamic conversion rows to columns SQL)

我有交易和卡之间的连接，简化它看起来像这样：

TranID  Date                    Card        ShopType    ShopName
11      2018-01-25 15:45:29.000 119317903   S           ShopA
12      2018-01-25 16:31:01.000 119317903   S           ShopB
13      2018-01-25 13:39:08.000 119325674   G           ShopC
14      2018-01-25 15:43:35.000 119325674   S           ShopA
15      2018-01-25 16:31:15.000 119325674   S           ShopD

我想创建一个新表，每卡一行，包括所有交易，该卡的详细信息。交易数量可以改变。因此，理想的结果是：

Card    TranID_1    Date_1  ShopType_1  ShopName_1  TranID_2    Date_2 ShopType_2   ShopName_2  TranID_3    Date_3  ShopType_3  ShopName_3
119317903   11      2018-01-25 15:45:29.000 S       ShopA   12  2018-01-25 16:31:01.000 S   ShopB               
119325674   13      2018-01-25 13:39:08.000 G       ShopC   14  2018-01-25 15:43:35.000 S   ShopA   15  2018-01-25 16:31:15.000 S   ShopD

我发现这对SO，但我不能完全得到动态SQL语法来为我工作。（动态SQL总是让我的更好）。

有效行转换为列在SQL Server

任何帮助将不胜感激。

提前致谢！

你可以通过执行动态SQL查询实现这一目标。

询问

declare @sql nvarchar(max);

select @sql = 'select [Card], ' + stuff((
        select distinct 
        ',min(case [sl_no] when ' + cast([sl_no] as varchar(100)) 
        + ' then [TranID] end) as [TranID_' + cast([sl_no] as varchar(100)) + ']' +
        ',min(case [sl_no] when ' + cast([sl_no] as varchar(100)) 
        + ' then [Date] end) as [Date_' + cast([sl_no] as varchar(100)) + ']' +
        ',min(case [sl_no] when ' + cast([sl_no] as varchar(100)) 
        + ' then [ShopType] end) as [ShopType_' + cast([sl_no] as varchar(100)) + ']' +
        ',min(case [sl_no] when ' + cast([sl_no] as varchar(100)) 
        + ' then [ShopName] end) as [ShopName_' + cast([sl_no] as varchar(100)) + ']' 
        from (
            select [sl_no] = row_number() over(
                partition by [Card] 
                order by [Date]
            ), * from [dbo].[tbl_name]
        ) as [t]
        for xml path('')
    )
    , 1, 1, ''
);

set @sql += ' from (select [sl_no] = row_number() over(partition by [Card] order by [Date]), 
            * from [dbo].[tbl_name]) as [t] group by [Card];';

exec(@sql);