Dynamic conversion rows to columns SQL

Question

I have got a JOIN between transactions and Cards, simplified it looks like this:

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

I want to create a new table with one row per card, including all transactions and details of that card. Number of transactions can vary. So desired result would be:

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

I Found this on SO, but I can't quite get the dynamic SQL syntax to work for me. (dynamic SQL always gets the better of me).

Efficiently convert rows to columns in sql server

Any help would be greatly appreciated.

Thanks in Advance!

score 0 · Accepted Answer · answered Jul 04 '18 at 08:18

0

You can achieve this by executing a dynamic sql query.

Query

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

answered Jul 04 '18 at 08:18

Ullas

11,450
4
33
50

Exactly why I posted this. I could not have achieved this myself. Syntax seems so complex to me. Thanks a lot @Wanderer, this indeed works! – SQL_M Jul 04 '18 at 13:00
Just one more quick question, probably over-asking here... :) I want to create a new table, so I would like to select this into a Table. I've tried several places, but have not succeeded. Where do I put the results table? – SQL_M Jul 04 '18 at 13:35
Yeah (even) I get that. But where: set@sql += 'into #Results' OR in the dynamic statement itself. Probably self explainatory for you, but I can't figure it out. – SQL_M Jul 05 '18 at 07:24
@SQL_M : if you are creating temp table using dynamic sql query, then that temp table will not be available out of the dynamic sql query. – Ullas Jul 05 '18 at 07:38
Thanks, I figured it out, finally: SET @Sql += ' INTO ##T' works. – SQL_M Jul 05 '18 at 08:43

Dynamic conversion rows to columns SQL

1 Answers1

Linked