A Possible Alternative to SQL UNPIVOT

Question

Post reply

A Possible Alternative to SQL UNPIVOT

paul_milligan01

SSC Enthusiast

Points: 155
More actions
January 15, 2017 at 10:13 pm

#325468

Comments posted to this topic are about the item A Possible Alternative to SQL UNPIVOT

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Manie Verster SSCertifiable Points: 7152 More actions · Answer 1

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)

amd.repetto Ten Centuries Points: 1300 More actions · Answer 2

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

R M Buda SSCrazy Points: 2225 More actions · Answer 3

amd.repetto - Monday, January 16, 2017 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.

Hi amd.repetto, I am curious to see a quick example of the OUTER APPLY + VALUES way of doing unpivot.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

Interesting technique Paul. Well done.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

amd.repetto Ten Centuries Points: 1300 More actions · Answer 5

R M Buda - Monday, January 16, 2017 5:23 AM
amd.repetto - Monday, January 16, 2017 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.
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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 6

paul_milligan01 - Sunday, January 15, 2017 10:12 PM
Comments 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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 7

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

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?

drew.allen SSC Guru Points: 76944 More actions · Answer 9

Jeff Moden - Monday, January 16, 2017 4:16 PM
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.

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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 10

drew.allen - Wednesday, January 18, 2017 1:04 PM
Jeff Moden - Monday, January 16, 2017 4:16 PM
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.
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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 11

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

R M Buda SSCrazy Points: 2225 More actions · Answer 12

Jeff Moden - Tuesday, January 17, 2017 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.

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)

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 13

Excellent. Thank you. Now for some testing! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alessandro Mortola SSC Eights! Points: 856 More actions · Answer 14

Very interesting article. In my opinion there could be a little problem in case you deal with a query containing one or more joins. In this case the resultant XML is not as flat as the one of the example. Look at the following code, for example:


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

The XML would be something like that, and the parsing operation would be more difficult.


<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>

The use of a CTE does not help. One possible workaround is to put the result of the query into a temporary table and then select from it.
Any other ideas?