1

How can I accomplish this with unpivot and pivot. I've seen this question asked before and has a solution with case statement and union all
In SQL, how can I count the number of values in a column and then pivot it so the column becomes the row? and here PIVOT/UNPIVOT multiple rows and columns but I have 20 rows and 24 columns and the query would become very long and I suspect inefficient. Does anyone know how I can do this with unpivot and pivot or is case and union all the only viable option?

Hour  A   B  C  D  E ... Z
-----------------------------------------
0     4   2  3  0  6     2
1     3   5  7  1  8     7
2     2   6  1  1  4     3
3     2   2  0  3  0     2
4     3   9  6  2  2     8
...
23    6   5  2  3  8     6


Field 0   1    2    3  ...23  
--------  --   --   -
A     2   0    2    2     4
B     7   2    8    1     6
....
Z     6   7    7    3     8

This is what I've tried in terms of pivot but I didn't get far:

select B,[0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23] from CTE2
pivot( 
sum(A)
for hour in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])) as pvt;

Just to clarify, the numbers in the table are just random numbers I've put to simulate data, they aren't transposed as they should be.

Community
  • 1
  • 1
Dong
  • 328
  • 1
  • 3
  • 16
  • I seem to have solved it with Zugwalt's solution in the first link I shared. I'll try to post my own answer later if I find time (it's 2:15 AM now) – Dong May 03 '16 at 06:12

1 Answers1

1

Well, I know you say you've solved it so this probably isn't necessary and you can feel free to use whatever answer you currently have, but here's an example of how you could approach this problem in general.

IF OBJECT_ID('tmpTable', 'U') IS NOT NULL DROP TABLE tmpTable;
CREATE TABLE tmpTable (i INT, a INT, b INT, c INT, d INT);
INSERT tmpTable VALUES (1,69,69,10,1)
     , (2,5,0,2,3)
     , (3,5,5,5,5)
     , (4,1,2,3,4);

DECLARE @applycols NVARCHAR(MAX);
SELECT @applycols = STUFF(
    (SELECT ',(' + QUOTENAME(COLUMN_NAME) + ', ''' + COLUMN_NAME + ''')'
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'tmpTable' 
    AND COLUMN_NAME <> 'i'
    FOR XML PATH ('')),1,1,'');

DECLARE @aggcols NVARCHAR(MAX) = '';
SELECT @aggcols += ', MAX(CASE WHEN i = ' + CAST(i AS NVARCHAR(255)) + ' THEN piv.colval END) ' + QUOTENAME(CAST(i AS NVARCHAR(255)))
FROM tmpTable;

DECLARE @SQL NVARCHAR(MAX) = 'SELECT piv.col' + @aggcols + '
FROM tmpTable
CROSS APPLY (VALUES ' + @applycols + ') piv(colval, col)
GROUP BY piv.col;';

EXEC(@SQL);

DROP TABLE tmpTable;

Essentially, it's using dynamic SQL to determine all the columns/values and then using a simple CROSS APPLY / MAX(CASE... to get all the values.

ZLK
  • 2,864
  • 1
  • 10
  • 7