Convert Vertical table data to Horizontal table data with multiple columns

• taschmitz

  SSC Rookie

  Points: 30

  More actions

  July 25, 2018 at 11:19 am

  #372519

  I have attribute data, that is coming to me in, what I am calling a vertical file(s).
  "attribute","meta","value"
  I need to get it, into a Horizontal table.
  I was able to combine the (3) vertical files into (1) table based on the ITEM_PIK. But, it is still vertical.

  Here is a sample of the table I created:
  (Some NOTEs: ITEM_PIK is like a part number. META_DATA is the ATTRIBUTE(name), META_DESC is ATTRIBUTE DESC, and VALUE is ATTRIBUTE Value)

  ITEM_PIK META_DATA            META_DESC         VALUE
  108528     Directory                    Global Attribute      CIS
  108528     Commerce Sector      Global Attribute     Construction Materials
  108534     Directory                     Global Attribute     CIS
  108534     Commerce Sector      Global Attribute     Construction Materials
  108534     Industry                      Global Attribute     Mill Supplies

  This is a clip of the data in the attached file.
  It is possible to have as many as 32 Attribute/Value pairs coming out of this data.
  The output data/table would look something like this:
  ITEM_PIK     ATTRIBUTE1   ATTR_DESC1    ATTR_VALUE1  ATTRIBUTE2  ATTR_DESC2  ATTR_VALUE2  ATTRIBUTE3 ATTR_DESC3 ATTR_VALUE3  ATTRIBUTE4  ATTR_DESC4  ATTR_VALUE4...…..ETC TO ATTRIBUTE32
  108528         Directory           Global Attribute   CIS                    Commerce Sector  Global Attribute  Construction Materials

  I have been trying to understand how to do this with PIVOT. But, I have not made any headway.
  Has anyone had to convert data like this before?

  Any help would be appreciated.

  Thank you

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 25, 2018 at 12:22 pm

  #1998976

  taschmitz - Wednesday, July 25, 2018 11:19 AM

  I have attribute data, that is coming to me in, what I am calling a vertical file(s).
  "attribute","meta","value"
  I need to get it, into a Horizontal table.
  I was able to combine the (3) vertical files into (1) table based on the ITEM_PIK. But, it is still vertical.

  Here is a sample of the table I created:
  (Some NOTEs: ITEM_PIK is like a part number. META_DATA is the ATTRIBUTE(name), META_DESC is ATTRIBUTE DESC, and VALUE is ATTRIBUTE Value)

  ITEM_PIK META_DATA            META_DESC         VALUE
  108528     Directory                    Global Attribute      CIS
  108528     Commerce Sector      Global Attribute     Construction Materials
  108534     Directory                     Global Attribute     CIS
  108534     Commerce Sector      Global Attribute     Construction Materials
  108534     Industry                      Global Attribute     Mill Supplies

  This is a clip of the data in the attached file.
  It is possible to have as many as 32 Attribute/Value pairs coming out of this data.
  The output data/table would look something like this:
  ITEM_PIK     ATTRIBUTE1   ATTR_DESC1    ATTR_VALUE1  ATTRIBUTE2  ATTR_DESC2  ATTR_VALUE2  ATTRIBUTE3 ATTR_DESC3 ATTR_VALUE3  ATTRIBUTE4  ATTR_DESC4  ATTR_VALUE4...…..ETC TO ATTRIBUTE32
  108528         Directory           Global Attribute   CIS                    Commerce Sector  Global Attribute  Construction Materials

  I have been trying to understand how to do this with PIVOT. But, I have not made any headway.
  Has anyone had to convert data like this before?

  Any help would be appreciated.

  Thank you

  Read the following articles that explain how to do it.
  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - SQLServerCentral
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - SQLServerCentral

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2