November 7, 2017 at 3:12 am
Hello,
I have a table like below, where headers of columns and rows are dynamic (this is a result of query)
| Group | Size | Price | Others |
| Summary | 43 | 34 | 9 |
| Main | 35 | 29 | 5 |
| Various | 2 | 1 | 1 |
| Special | 5 | 2 | 3 |
| Turbo | 1 | 2 | -1 |
Is there a way in SQL to "rotate' that table on below way:
| Summary | Main | Various | Special | Turbo | |
| Size | 43 | 35 | 2 | 5 | 1 |
| Price | 24 | 29 | 1 | 2 | 2 |
| Others | 9 | 5 | 1 | 3 | -1 |
I was able to use CURSORS to achieve the results, but I am wondering if there is maybe easier one, eg.: using PIVOT nad UNPIVOT.
Thank you!
November 7, 2017 at 10:02 am
psobanski - Tuesday, November 7, 2017 3:12 AMHello,
I have a table like below, where headers of columns and rows are dynamic (this is a result of query)
Group Size Price Others Summary 43 34 9 Main 35 29 5 Various 2 1 1 Special 5 2 3 Turbo 1 2 -1 Is there a way in SQL to "rotate' that table on below way:
Summary Main Various Special Turbo Size 43 35 2 5 1 Price 24 29 1 2 2 Others 9 5 1 3 -1 I was able to use CURSORS to achieve the results, but I am wondering if there is maybe easier one, eg.: using PIVOT nad UNPIVOT.
Thank you!
Can you post your data in a consumable format? That means DDL and insert statements. If you need help on how to do it, check the articles on my signature. To achieve your goal, you need to unpivot and then pivot the data, but I won't suggest using those operators. Instead, I suggest using what is explained in the following articles:
An Alternative (Better?) Method to UNPIVOT (SQL Spackle) - SQLServerCentral
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - SQLServerCentral
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - SQLServerCentral
If you post the consumable sample data, I can give an example on how to apply the techniques from the articles.
November 8, 2017 at 3:03 am
Dear Luis,
Thank you for your response. One thing to be clarified. The list of columns in input table is known (not dynamic as I mentioned before). The values in column [Group] is dynamic and its fields should be the columns of "rotated table"
Here is the code to generate the input table:
create table #SummaryTable
(
[Group] varchar (48),
Size float,
Price float,
Others float
)
insert into #SummaryTable ([Group], Size, Price, Others) values
('Summary', 43, 34, 9),
('Main', 35, 29, 5),
('Various', 2, 1, 1),
('Special', 5, 2, 3),
('Turbo', 1, 2, -1)
select * from #SummaryTable
drop table #SummaryTable
November 8, 2017 at 4:46 am
psobanski - Wednesday, November 8, 2017 3:03 AMDear Luis,
Thank you for your response. One thing to be clarified. The list of columns in input table is known (not dynamic as I mentioned before). The values in column [Group] is dynamic and its fields should be the columns of "rotated table"
Here is the code to generate the input table:
create table #SummaryTable
(
[Group] varchar (48),
Size float,
Price float,
Others float
)insert into #SummaryTable ([Group], Size, Price, Others) values
('Summary', 43, 34, 9),
('Main', 35, 29, 5),
('Various', 2, 1, 1),
('Special', 5, 2, 3),
('Turbo', 1, 2, -1)select * from #SummaryTable
drop table #SummaryTable
I think this will do what you need:
DECLARE @SQL VARCHAR(MAX);
SELECT
@SQL
= 'WITH unpvt AS (SELECT s.[Group], x.col, x.val FROM #SummaryTable s CROSS APPLY (VALUES (''Size'', Size), (''Price'', Price), (''Others'', Others)) x (col, val)) '
+ 'SELECT col AS NewGroup '
+
(
SELECT
', MAX (CASE WHEN [Group] = ' + QUOTENAME([Group], '''') + ' THEN val ELSE NULL END) AS ' + QUOTENAME([Group])
FROM #SummaryTable
FOR XML PATH('')
).value('.', 'varchar(max)') + ' FROM unpvt GROUP BY unpvt.col ORDER BY NewGroup DESC;';
--SELECT @SQL; -- to test the query
EXEC (@SQL);
Thomas Rushton
blog: https://thelonedba.wordpress.com
November 8, 2017 at 7:47 am
Some small improvements to Thomas' code. Even with dynamic code, I like to keep it with a good format to ease the debugging.
I'm also using sp_executesql which would allow to use parameters in case they're needed.
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = N'WITH unpvt AS (' + CHAR(13)
+ CHAR(9) + N'SELECT s.[Group]' + CHAR(13)
+ CHAR(9) + CHAR(9) + N',x.col' + CHAR(13)
+ CHAR(9) + CHAR(9) + N',x.val ' + CHAR(13)
+ CHAR(9) + N'FROM #SummaryTable s' + CHAR(13)
+ CHAR(9) + N'CROSS APPLY (VALUES (''Size'', Size), (''Price'', Price), (''Others'', Others)) x (col, val)' + CHAR(13)
+ N') ' + CHAR(13)
+ N'SELECT col AS NewGroup ' + CHAR(13)
+ (
SELECT
CHAR(9) + ', MAX (CASE WHEN [Group] = ' + QUOTENAME([Group], '''') + ' THEN val ELSE NULL END) AS ' + QUOTENAME([Group]) + CHAR(13)
FROM #SummaryTable
FOR XML PATH(''), TYPE
).value('./text()[1]', 'Nvarchar(max)')
+ N' FROM unpvt ' + CHAR(13)
+ N'GROUP BY unpvt.col ' + CHAR(13)
+ N'ORDER BY NewGroup DESC;';
PRINT @SQL; -- to test the query
EXEC sp_executesql @SQL;
November 8, 2017 at 10:01 am
November 8, 2017 at 10:02 am
November 8, 2017 at 10:06 am
Luis Cazares - Wednesday, November 8, 2017 7:47 AMThank you!
You're welcome. Now the important question is: Do you understand how this works?
Remember that you'll be the one in charge of supporting this solution.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply