代码之家  ›  专栏  ›  技术社区  ›  SQL_M

行到列的动态转换SQL

  •  0
  • SQL_M  · 技术社区  · 8 年前

    我在交易和卡之间有一个连接,简化如下:

    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
    

    我是这样发现的,但是我不能完全使用动态SQL语法。(动态SQL总是比我强)。

    Efficiently convert rows to columns in sql server

    任何帮助都将不胜感激。

    事先谢谢!

    1 回复  |  直到 8 年前
        1
  •  0
  •   Ullas    8 年前

    您可以通过执行动态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);