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 :
data | Jan'23 | Feb'23 |
---|---|---|
Column_A | 12000000 | 130000 |
Column_B | 11000000 | 120000 |
LU | 25 | 22 |
Is there multi dynamic pivot?
1条答案
按热度按时间whlutmcx1#
Here's an example for you to learn:
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.