Results 1 to 4 of 4

Thread: Performing an Unpivot - SQL Server 2012

  1. #1

    Thread Starter
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,829

    Performing an Unpivot - SQL Server 2012

    I've got an xml snippet that I'm extracting into a row.
    Sample:
    Code:
    <TABLEDATA>
      <TABLEROW>
        <ID1>{guid1}</ID1>
        <VALUE1>Some data 1</VALUE1>
        <ID2>{guid2}</ID2>
        <VALUE2>Some data 2</VALUE2>
        <ID3>{guid3}</ID3>
        <VALUE3>Some data 3</VALUE3>
        <ID4>{guid4}</ID4>
        <VALUE4>Some data 4</VALUE4>
        <ID5>{guid5}</ID5>
        <VALUE5>Some data 5</VALUE5>
      </TABLEROW>
    </TABLEDATA>
    I'm able to extract the data and get it into a row:
    Code:
    ID1       VALUE1         ID2       VALUE2         ID3       VALUE3         ID4       VALUE4         ID5       VALUE5
    {guid1} Some data1  {guid2} Some data2   {guid3} Some data3  {guid4} Some data4   {guid5} Some data5
    I'm trying to rotate the data so that all of the IDs are in one col, ID, and all the related Values are in one col, Values:
    Code:
    ID         VALUE
    {guid1} Some data1  
    {guid2} Some data2   
    {guid3} Some data3  
    {guid4} Some data4   
    {guid5} Some data5
    For some reason I'm having trouble wrapping my head around this.
    All of the examples I've found so far unpivot around one col and the rest of the data is denormalized to the cols... but here I'm trying to unpivot across two cols. I tried a double unpivot (two unpivot clauses) and I ended up with a cartesian effect.... ugh... not what I need or want.

    As soon as I get back onto the machine where I'm working off of, I'll post the xml and shredding and the unpivoting I've tried.

    Restriction - this is being done in a view so dumping the data into a temp table of any kind is out of the question. I think I might be able to use a multi-statement table function though.... hmmm... any ideas?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  2. #2

    Thread Starter
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,829

    Re: Performing an Unpivot - SQL Server 2012

    Ungh... of course as soon as I pull it all up and get it ready to copy here... the [insert colorful language] laptop died... and it didn't bother to go to sleep or hibernate.... nooooo.... that would have made too much sense. So instead it decided to just up and quit, losing all of the work I had. Re-creating the XML was easy, but I lost the unpivot I had.

    Maybe it was just as well... I think I found an alternate solution using a CTE and some union queries. feels like a kudge, but time is critical, and I just need to get it up and working. The rest of is isn't pretty anyways.

    Here's the XML:
    xml Code:
    1. <MAPPEDVALUES>
    2.   <ROW>
    3.     <ID>99999999-9999-9999-9999-999999999999</ID>
    4.     <ACCOUNTOFFICEID>1A692E94-3214-44B8-A658-F2927230BC25</ACCOUNTOFFICEID>
    5.     <REVENUETYPEID>E94C5D99-45C3-4832-9394-E52D4639438C</REVENUETYPEID>
    6.     <APPLICATIONTYPEID>ECA6E4A7-8B0B-4E80-BAFF-07A3559E4AD4</APPLICATIONTYPEID>
    7.     <PAYMENTMETHODID>C24896E3-F243-45A1-BF2C-DA333C2B7F44</PAYMENTMETHODID>
    8.     <ACCOUNTSYSTEMID>4B121C2C-CCE6-440D-894C-EA0DEF80D50B</ACCOUNTSYSTEMID>
    9.     <SEGMENT02TABLEID>1BA6F27F-8454-4FC4-A830-3FDA8CD35D04</SEGMENT02TABLEID>
    10.     <SEGMENT03ID>68F1B5E8-9BD5-4D9B-B2F0-12F1A7E347B6</SEGMENT03ID>
    11.     <SEGMENT03TABLEID>3298E2CE-EF37-4681-AC12-050979B6C5F3</SEGMENT03TABLEID>
    12.     <SEGMENT04ID>6300D932-0E86-4C2C-BE09-6791F5E41E55</SEGMENT04ID>
    13.     <SEGMENT04TABLEID>AEBA8FD5-42B6-4D37-93EB-8916BEC1385A</SEGMENT04TABLEID>
    14.     <SEGMENT05TABLEID>3EF5D061-5692-40F3-B8AD-1367A8C6FAD5</SEGMENT05TABLEID>
    15.     <SEGMENT06ID>A044FAD4-D902-4D56-B197-FBD8877C9AE2</SEGMENT06ID>
    16.     <SEGMENT06TABLEID>5B93DEF5-AAA7-482E-B8F7-169EE0DAA53D</SEGMENT06TABLEID>
    17.     <ERRORMESSAGE></ERRORMESSAGE>
    18.     <ERRORCODE>0</ERRORCODE>
    19.     <HASHVALUE>0</HASHVALUE>
    20.     <MD5HASHVALUE>087C5BE7-2DFD-A5B4-5822-288EC0A2DDC1</MD5HASHVALUE>
    21.   </ROW>
    22. </MAPPEDVALUES>
    the fields I'm interested in are the Segment nodes, 02-06 ... there's a table ID for all of the segments, but not all have an ID (no value).
    For testing, Im putting it into an XML variable, then using this to parse it out... this gives me the results I want, Table IDs in one col and the other IDs in another.

    sql Code:
    1. declare @GLXML xml = '<MAPPEDVALUES>
    2.   <ROW>
    3.     <ID>99999999-9999-9999-9999-999999999999</ID>
    4.     <PDACCOUNTCODEMAPOFFICEID>1A692E94-3214-44B8-A658-F2927230BC25</PDACCOUNTCODEMAPOFFICEID>
    5.     <REVENUETYPEID>E94C5D99-45C3-4832-9394-E52D4639438C</REVENUETYPEID>
    6.     <APPLICATIONTYPEID>ECA6E4A7-8B0B-4E80-BAFF-07A3559E4AD4</APPLICATIONTYPEID>
    7.     <PAYMENTMETHODID>C24896E3-F243-45A1-BF2C-DA333C2B7F44</PAYMENTMETHODID>
    8.     <PDACCOUNTSYSTEMID>4B121C2C-CCE6-440D-894C-EA0DEF80D50B</PDACCOUNTSYSTEMID>
    9.     <SEGMENT02TABLEID>1BA6F27F-8454-4FC4-A830-3FDA8CD35D04</SEGMENT02TABLEID>
    10.     <SEGMENT03ID>68F1B5E8-9BD5-4D9B-B2F0-12F1A7E347B6</SEGMENT03ID>
    11.     <SEGMENT03TABLEID>3298E2CE-EF37-4681-AC12-050979B6C5F3</SEGMENT03TABLEID>
    12.     <SEGMENT04ID>6300D932-0E86-4C2C-BE09-6791F5E41E55</SEGMENT04ID>
    13.     <SEGMENT04TABLEID>AEBA8FD5-42B6-4D37-93EB-8916BEC1385A</SEGMENT04TABLEID>
    14.     <SEGMENT05TABLEID>3EF5D061-5692-40F3-B8AD-1367A8C6FAD5</SEGMENT05TABLEID>
    15.     <SEGMENT06ID>A044FAD4-D902-4D56-B197-FBD8877C9AE2</SEGMENT06ID>
    16.     <SEGMENT06TABLEID>5B93DEF5-AAA7-482E-B8F7-169EE0DAA53D</SEGMENT06TABLEID>
    17.     <ERRORMESSAGE></ERRORMESSAGE>
    18.     <ERRORCODE>0</ERRORCODE>
    19.     <HASHVALUE>0</HASHVALUE>
    20.     <MD5HASHVALUE>087C5BE7-2DFD-A5B4-5822-288EC0A2DDC1</MD5HASHVALUE>
    21.   </ROW>
    22. </MAPPEDVALUES>'
    23.  
    24. ; with GLSEGMENTS as (
    25.     SELECT
    26.         T.c.value('(SEGMENT02TABLEID)[1]', 'uniqueidentifier') AS SEGMENT02TABLEID,
    27.         T.c.value('(SEGMENT02ID)[1]', 'uniqueidentifier') AS SEGMENT02ID,
    28.         T.c.value('(SEGMENT03TABLEID)[1]', 'uniqueidentifier') AS SEGMENT03TABLEID,
    29.         T.c.value('(SEGMENT03ID)[1]', 'uniqueidentifier') AS SEGMENT03ID,
    30.         T.c.value('(SEGMENT04TABLEID)[1]', 'uniqueidentifier') AS SEGMENT04TABLEID,
    31.         T.c.value('(SEGMENT04ID)[1]', 'uniqueidentifier') AS SEGMENT04ID,
    32.         T.c.value('(SEGMENT05TABLEID)[1]', 'uniqueidentifier') AS SEGMENT05TABLEID,
    33.         T.c.value('(SEGMENT05ID)[1]', 'uniqueidentifier') AS SEGMENT05ID,
    34.         T.c.value('(SEGMENT06TABLEID)[1]', 'uniqueidentifier') AS SEGMENT06TABLEID,
    35.         T.c.value('(SEGMENT06ID)[1]', 'uniqueidentifier') AS SEGMENT06ID
    36.     FROM   @GLXML.nodes('/MAPPEDVALUES/ROW') T(c)
    37. )
    38. select SEGMENT02TABLEID as TABLEID, SEGMENT02ID as VALUEID from GLSEGMENTS
    39. union all
    40. select SEGMENT03TABLEID as TABLEID, SEGMENT03ID as VALUEID from GLSEGMENTS
    41. union all
    42. select SEGMENT04TABLEID as TABLEID, SEGMENT04ID as VALUEID from GLSEGMENTS
    43. union all
    44. select SEGMENT05TABLEID as TABLEID, SEGMENT05ID as VALUEID from GLSEGMENTS
    45. union all
    46. select SEGMENT06TABLEID as TABLEID, SEGMENT06ID as VALUEID from GLSEGMENTS

    I'm using a CTE to shred the XML, then selecting each pair from the CTE and unioning it all together.
    Luckily there's only the 5 segments (Segment 01 is a built-in hidden segment). so it keeps the unions small... but it just doesn't feel right or natural.

    I think I can build a TF around this so I can pass in the XML (which actually comes from a function code elsewhere... this whole query is a mess, but it works, and I'm already 20 hrs over budget on it due to something else that "sounded simple at the time"... until I looked into the mechanics of it and how the system dealt with it... ungh... there are times when I wonder what in the world were they thinking when they designed this stuff... they certainly don't think about us in trenches that has to then contend with the results.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,829

    Re: Performing an Unpivot - SQL Server 2012

    I'm going to go ahead and mark this thread as resolved... I've got something that will fit my needs. Turns out I was over thinking and over engineering it once again. I discovered that I don't need to unpivot or union any of the data, I just needed to shred the XML back into a single row and then deal with it accordingly. It just took me being up half the night staring at it to figure this out. Once I unioned the data as I did above, I realized I was losing the context... and then I ended up with multiple rows in my result, which isn't the desired effect. Sigh.

    So the solution ended up being: shred the XML into a single row, then join it to another CTE multiple times for the different segments. Part of what threw me was that the individual segment numbers (02-06) weren't in the same order I was expecting. The ordering is tracked in a different area, and the part of the system that creates these segments doesn't care what order they end up in, it just knows it needs to create the segments, and it's someone (or something) else's job to sort them into the right order. Yay.

    So I'm marking this resolved. Sometimes all I need to do is just talk it out before the solution presents itself.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,285

    Re: Performing an Unpivot - SQL Server 2012

    Code:
    SELECT T1.Value AS TABLEID,T2.Value AS VALUEID FROM
    (
    	select  C.value('local-name(.)', 'varchar(50)') as NodeName,
    			C.value('(.)[1]', 'varchar(50)') as Value
    	FROM @GLXML.nodes('/MAPPEDVALUES/ROW/*') AS T(C)
    ) T1
    LEFT JOIN
    (
    	select  C.value('local-name(.)', 'varchar(50)') as NodeName,
    			C.value('(.)[1]', 'varchar(50)') as Value
    	FROM @GLXML.nodes('/MAPPEDVALUES/ROW/*') AS T(C)
    ) T2 
    ON T2.NodeName LIKE 'SEGMENT__ID' AND T1.NodeName LIKE 'SEGMENT__TABLEID' AND LEFT(T1.NodeName,9)=LEFT(T2.NodeName,9)
    WHERE T1.NodeName LIKE 'SEGMENT__TABLEID'

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width