January 15, 2017 at 10:13 pm
Comments posted to this topic are about the item A Possible Alternative to SQL UNPIVOT
January 15, 2017 at 11:06 pm
Thank you very much. Very informative indeed.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
January 16, 2017 at 4:43 am
Hi! Thanks for the alternatives 🙂 but I think you've missed the OUTER APPLY + VALUES option. it may be the simplest way of doing an unpivot.
A. Mauricio Repetto
ML Engineer
January 16, 2017 at 5:23 am
amd.repetto - Monday, January 16, 2017 4:43 AMHi! Thanks for the alternatives 🙂 but I think you've missed the OUTER APPLY + VALUES option. it may be the simplest way of doing an unpivot.
Hi amd.repetto, I am curious to see a quick example of the OUTER APPLY + VALUES way of doing unpivot.
January 16, 2017 at 5:42 am
Interesting technique Paul. Well done.
-- Itzik Ben-Gan 2001
January 16, 2017 at 6:16 am
R M Buda - Monday, January 16, 2017 5:23 AMamd.repetto - Monday, January 16, 2017 4:43 AMHi! Thanks for the alternatives 🙂 but I think you've missed the OUTER APPLY + VALUES option. it may be the simplest way of doing an unpivot.Hi amd.repetto, I am curious to see a quick example of the OUTER APPLY + VALUES way of doing unpivot.
Here you have Buda 🙂 I always use this approach for unpivoting data: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
A. Mauricio Repetto
ML Engineer
January 16, 2017 at 4:16 pm
paul_milligan01 - Sunday, January 15, 2017 10:12 PMComments posted to this topic are about the item A Possible Alternative to SQL UNPIVOT
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
,ColA INT
,ColB INT
,ColC INT
)
;
--===== 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
('ColA',ColA)
,('ColB',ColB)
,('ColC',ColC)
) 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. StatisticName ProductCount
------------------- ------------
ProductsWithNoPrice 3
Products500OrLess 4
ProductsOver1500 6
TotalCount 14
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2017 at 6:38 pm
Not sure that the author of this article can post back so can anyone else answer my question at the top of the previous post? I'd like to do a bit of a performance test. You never know where you might find a golden nugget.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2017 at 12:09 pm
I do like the flow. One thing though - your intro is defining how to *pivot* (how to turn 3 rows into columns), NOT how to un-pivot. A bit silly - but it can be a little confusing if you're not familiar with the concept since your technique section then describe turning the columns back into rows (which is the actual unpivot part).<b
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 18, 2017 at 1:04 pm
Jeff Moden - Monday, January 16, 2017 4:16 PMFor 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.
You do not need a unique column in order to unpivot. This can easily be seen by just removing the RowNum column from your test table and results. The reason to have a unique column is to re-pivot once you have unpivoted.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 18, 2017 at 5:31 pm
drew.allen - Wednesday, January 18, 2017 1:04 PMJeff Moden - Monday, January 16, 2017 4:16 PMFor 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.
You do not need a unique column in order to unpivot. This can easily be seen by just removing the RowNum column from your test table and results. The reason to have a unique column is to re-pivot once you have unpivoted.
Drew
Yep... understood. That's why you actually do need a unique column to unpivot. 😉 Makes little sense, otherwise.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2017 at 5:34 pm
Just to ask the question again, would someone show me how to apply this XML method of unpivoting to a whole table instead of just one row? Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2017 at 7:07 am
Jeff Moden - Tuesday, January 17, 2017 6:38 PMNot sure that the author of this article can post back so can anyone else answer my question at the top of the previous post? I'd like to do a bit of a performance test. You never know where you might find a golden nugget.
If you want to use the XML technique to unpivot only a subset of columns, you need to convert the columns you want to unpivot into an XML column, leaving the other (identifying) columns as normal. Then the XML functions can do their magic.
Here is your example:
--===== 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
,ColA INT
,ColB INT
,ColC INT
)
;
--===== 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
;
;
WITH TableWithXmlCol AS
(
SELECT RowNum,
(SELECT
ColA,
ColB,
ColC
FROM #TestTable P
WHERE P2.RowNum = P.RowNum
FOR XML AUTO, ELEMENTS, TYPE
) AS XmlCol
FROM #TestTable P2
)
SELECT
TableWithXmlCol.RowNum,
ColName = T.c.value('fn:local-name(.)', 'sysname'),
ColVal = T.c.value('.', 'varchar(20)')
FROM TableWithXmlCol
CROSS APPLY TableWithXmlCol.XmlCol.nodes('//P/*') T(c)
January 19, 2017 at 8:20 am
Excellent. Thank you. Now for some testing! 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 28, 2017 at 2:56 am
CREATE TABLE A1 (Id INT, Agent varchar(20))
CREATE TABLE R1 (AgentId INT, Revenue2014 INT, Revenue2015 INT, Revenue2016 INT) INSERT INTO A1 VALUES
(1, 'John'),
(2, 'Paul'),
(3, 'Alex')INSERT INTO R1 VALUES
(1, 100, 200, 250),
(2, 120, 80, 95),
(3, 50, 70, 150)SELECT Agent, Revenue2014, Revenue2015, Revenue2016
FROM A1
INNER JOIN R1 ON A1.Id = R1.AgentId
FOR XML AUTO, ELEMENTS, TYPE
<A1>
<Agent>John</Agent>
<R1>
<Revenue2014>100</Revenue2014>
<Revenue2015>200</Revenue2015>
<Revenue2016>250</Revenue2016>
</R1>
</A1>
<A1>
<Agent>Paul</Agent>
<R1>
<Revenue2014>120</Revenue2014>
<Revenue2015>80</Revenue2015>
<Revenue2016>95</Revenue2016>
</R1>
</A1>
<A1>
<Agent>Alex</Agent>
<R1>
<Revenue2014>50</Revenue2014>
<Revenue2015>70</Revenue2015>
<Revenue2016>150</Revenue2016>
</R1>
</A1>
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply