Dynamic multiple pivot in SQL Server?

eagi6jfj  于 2023-03-17  发布在  SQL Server
关注(0)|答案(1)|浏览(146)

I have a table like :
| Date | Column_A | Column_B | LU |
| ------------ | ------------ | ------------ | ------------ |
| Jan'23 | 12000000 | 130000 | 25 |
| Feb'23 | 11000000 | 120000 | 22 |

I want to change it like this :

dataJan'23Feb'23
Column_A12000000130000
Column_B11000000120000
LU2522

Is there multi dynamic pivot?

whlutmcx

whlutmcx1#

Here's an example for you to learn:

select *
into #t
from (
    VALUES  (N'Jan''23', 12000000, 130000, 25)
    ,   (N'Feb''23', 11000000, 120000, 22)
) t (Date,Column_A,Column_B,LU)

declare @cols nvarchar(max) = ''
,   @SQL NVARCHAR(MAX)
select @cols = @cols + ',' + QUOTENAME(date)
from (
    select  date
    from    #t
    GROUP BY date
    ) x

SET @SQL = '
    SELECT  data ' + @cols + N'
    FROM    (
        SELECT  data, value, date
        FROM    #t
        CROSS APPLY (
            VALUES(''Column_a'', Column_A)
            ,   (''Column_b'', Column_B)
            ,   (''LU'', LU)
            ) d (data, value)
        GROUP BY data, value, date
        ) x
    PIVOT (MAX(value) FOR Date IN (' + STUFF(@cols, 1, 1, '') +  ') ) pv '
exec(@sql)

First you get unique pivot dates into the @cols variable. You can also use STRING_AGG to do this if you're on newer sql server. One problem is sorting, Feb'23 sorts ahead of Jan'23. But hopefully you use proper dates or have another way of sorting them.

Then, for the dynamic SQL, I unpivot the columns inside a value construct so i get the data in form of: date tagname tagvalue date2 tagname tagvalue2

If values have different datatypes, you might need to cast them to nvarchar(max).

This sets the stage for the final pivot that does the aggregation. I used MAX since you might have strings there. @cols will contain name of pivot columns in form of [Jan'23],[Feb'23], this is needed for pivot syntax to work.

If you use real date columns, watch out since converting date to string might lose information you need to do the pivot properly.

If you want to have dynamic number of columns in your table, then you just have to repeat this process when you generate the VALUES(tagname, tagvalue) sql.

In general, don't do this in SQL, unless you can handle all the complexities.

相关问题