I'm terrible with XML and wouldn't presume to try to convert your code from using a single row to unpivot to unpivoting a table. That being said, would you post some code that will unpivot the following table please? The example return that I'm looking for can be found in the code after the test table creation code.
--===== If the test table already exists, drop it to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
--===== Create a test table with a PK in place
CREATE TABLE #TestTable
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
--===== Populate the table using minimal logging if not in FULL Recovery Model
INSERT INTO #TestTable WITH (TABLOCK)
(ColA, ColB, ColC)
SELECT TOP 100000
ColA = ABS(CHECKSUM(NEWID())%1000)+1
,ColB = ABS(CHECKSUM(NEWID())%1000)+1
,ColC = ABS(CHECKSUM(NEWID())%1000)+1
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
For those interested in how to unpivot a multi-row table, you do need some form of unique column, which is RowNum in the example table above. It doesn't have to be either the PK or a Clustered Index or an IDENTITY column but it does need to be unique.
From there, it's a nearly trivial exercise as seen in the following code:
--===== Un-Pivot the columns using a CROSS APPLY and VALUES.
SELECT tt.RowNum, ca.ColName, ca.ColValue
FROM #TestTable tt
CROSS APPLY (VALUES
) ca (ColName,ColValue)
Also, I realize that the aggregations of the data isn't the primary focus of this article but you might want to add a WHERE clause so the COUNT in the following results from near the end of the article comes up with the correct answer.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)