Pivot Key-Value pairs

• SQLCurious

  SSCrazy

  Points: 2339

  More actions

  April 6, 2017 at 5:10 pm

  #336790

  Hello,

  Here is an image of the result sets from my existing tables and the desired result set I am looking to produce from a query. It is basically pivoting values from key-value pairs by deriving the resultant column names dynamically (not knowing what the complete set would be). I would appreciate your help with this.

  

  Here is the SQL to get my master table data and key-value pair data...

  
DECLARE @Company TABLE (CompanyID INT, CompanyName VARCHAR (100))
DECLARE @KVPairs TABLE (CompanyID INT, KeyColumn VARCHAR (100), ValueColumn VARCHAR (100))

  INSERT @Company VALUES (1001, 'Microsoft Corporation'), (1002, 'Apple Inc'), (1003, 'Amazon')
INSERT @KVPairs values
            (1001, 'OfficeID', '204'), (1001, 'OfficeName', 'Seattle Office'),
            (1001, 'DivisionTypeID', '301'), (1001, 'DivisionType', 'Corporate Head Quarters'),
            (1001, 'FacilityTypeID', '401'), (1001, 'FacilityType', 'Company Owned'),

              (1002, 'OfficeID', '203'), (1002, 'OfficeName', 'CA Office'),
            (1002, 'DivisionTypeID', '301'), (1002, 'DivisionType', 'Corporate Head Quarters'),
            (1002, 'FacilityTypeID', '401'), (1002, 'FacilityType', 'Company Owned'),

              (1003, 'OfficeID', '202'), (1003, 'OfficeName', 'NY Office'),
            (1003, 'DivisionTypeID', '302'), (1003, 'DivisionType', 'Sales Division'),
            (1003, 'FacilityTypeID', '403'), (1003, 'FacilityType', 'Rented')


  Thanks in advance,
  SQLCurious

• DesNorton

  SSC-Insane

  Points: 24284

  More actions

  April 6, 2017 at 10:24 pm

  #1937167

  This should do the trick

  
CREATE TABLE #Company (CompanyID INT, CompanyName VARCHAR (100));
CREATE TABLE #KVPairs (CompanyID INT, KeyColumn VARCHAR (100), ValueColumn VARCHAR (100));

  INSERT #Company VALUES (1001, 'Microsoft Corporation'), (1002, 'Apple Inc'), (1003, 'Amazon')
INSERT #KVPairs values
    (1001, 'OfficeID', '204'), (1001, 'OfficeName', 'Seattle Office'),
    (1001, 'DivisionTypeID', '301'), (1001, 'DivisionType', 'Corporate Head Quarters'),
    (1001, 'FacilityTypeID', '401'), (1001, 'FacilityType', 'Company Owned'),

      (1002, 'OfficeID', '203'), (1002, 'OfficeName', 'CA Office'),
    (1002, 'DivisionTypeID', '301'), (1002, 'DivisionType', 'Corporate Head Quarters'),
    (1002, 'FacilityTypeID', '401'), (1002, 'FacilityType', 'Company Owned'),

      (1003, 'OfficeID', '202'), (1003, 'OfficeName', 'NY Office'),
    (1003, 'DivisionTypeID', '302'), (1003, 'DivisionType', 'Sales Division'),
    (1003, 'FacilityTypeID', '403'), (1003, 'FacilityType', 'Rented');

  DECLARE
  @query NVARCHAR(MAX)
, @cols NVARCHAR(MAX);

  SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(kvp.KeyColumn)
       FROM #KVPairs AS kvp
       FOR XML PATH(''), TYPE
       ).value('(./text())[1]', 'NVARCHAR(MAX)')
      ,1,1,'')

  SET @query = 'SELECT CompanyID, CompanyName, ' + @cols + '
     FROM (
      SELECT c.CompanyID, c.CompanyName, kvp.KeyColumn, kvp.ValueColumn
      FROM #Company AS c
      INNER JOIN #KVPairs AS kvp
      ON c.CompanyID = kvp.CompanyID
     ) AS src
     PIVOT (
      MAX(ValueColumn)
      FOR KeyColumn in (' + @cols + ')
     ) AS pvt;'

  --PRINT(@query);
EXECUTE(@query);

  DROP TABLE #Company;
DROP TABLE #KVPairs;

  ---

  How to post data/code on a forum to get the best help.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  April 7, 2017 at 6:30 am

  #1937193

  SQLCurious,

  Just an FYI, but what you described with your pictures is known as an EAV design, or Entity, Attribute, Value.   For database querying, it's pretty much the worst possible database design.   If you have ANY influence over a choice of methodology, use it to get that design undone and replaced with something that normalizes the data.   Stick with it and every complex query will turn into another nightmare.   Forewarned is forearmed.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)