Pivot from row-level without loosing the ColNames

• info 58414

  Hall of Fame

  Points: 3386

  More actions

  December 12, 2018 at 10:26 am

  #369366

  Hi,
  i need to transform this data from row-level to columnar data.
  Pivot with ColName and ColValue

  Regards
  Nicole

  ColName	ColValue
  itemClass	yard
  itemType	SAL
  employeeId	tony
  stationId	800
  stage	PWT
  subStage	AVM

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  December 12, 2018 at 11:24 am

  #2016137

  This is obviously a continuation of your previous thread.  You should just learn how to shred XML documents correctly in the first place instead of trying to cobble together a mishmash of approaches from a multitude of forum posts that only present a partial picture of your overall problem.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  December 13, 2018 at 4:49 pm

  #2016267

  Try something like this:
  WITH
 t
(ColName, ColValue)
AS
 (
  SELECT
   b
   , c
  FROM
   (
    VALUES
     ( 'itemClass'
      , 'yard'
     )
     , ( 'itemType'
      , 'SAL'
     )
     , ( 'employeeId'
      , 'tony'
     )
     , ( 'stationId'
      , '800'
     )
     , ( 'stage'
      , 'PWT'
     )
     , ( 'subStage'
      , 'AVM'
     )
   ) a (b, c)
 )
,
 c
(itemClass, itemType, employeeId, stationId, stage, subStage)
AS
 (
  SELECT
   itemClass = CASE WHEN ColName = 'itemClass' THEN ColValue END
   , itemType = CASE WHEN ColName = 'itemType' THEN ColValue END
   , employeeId = CASE WHEN ColName = 'employeeId' THEN ColValue END
   , stationId = CASE WHEN ColName = 'stationId' THEN ColValue END
   , stage  = CASE WHEN ColName = 'stage' THEN ColValue END
   , subStage = CASE WHEN ColName = 'subStage' THEN ColValue END
  FROM
   t
 )
SELECT Max(c.itemClass ) itemClass
  , Max(c.itemType ) itemType
  , Max(c.employeeId) employeeId
  , Max(c.stationId ) stationId
  , Max(c.stage  ) stage 
  , Max(c.subStage ) subStage
FROM c;