I have a table with three columns, “Partnumber”, “Specifications” and “Value”, I need to create a table with all data as VARCHAR in which each value in the column “Specifications” appears as Header.
I tried to use PIVOT but I didn’t understand it very well (I’m a newbie); I’m using SQLServer.
These are the first rows in the original table:
Partnumber|Specifications|Value| 181504825 |Rotation |CW | 1541 |Belt Width (_)|7/16 | 156938 |Depth (mm) |73 | 1522231 |Grooves |6 | 1522236 |Grooves |4 | 1522348 |Grooves |4 | 1541 |Grooves |1 | 181504825 |Height (mm) |107.95| 156938 |Inlet (mm) |11.5 |
I need to create a new table like this:
Partnumber|Belt Width (_)|Depth (mm)|Grooves |Height (mm)|Inlet (mm)|Rotation| 1522231 | | |6 | | | | 1522236 | | |4 | | | | 1522348 | | |4 | | | | 1541 |7/16 | |1 | | | | 156938 | |73 | | |11.5 | | 181504825 | | | |107.95 | |CW |
Each Partnumber and the headers must appear without duplicates and in order, some Partnumber can appear without information and I can delete the row later. I would like to mention that the column “Specifications” contains 981 headers after deleting duplicates.
Advertisement
Answer
use conditional aggregation
select Partnumber, max(case when Specifications='Rotation' then value end) as Rotation, max(case when Specifications='Belt Width (_)|' then value end) as BeltWidth (_)|, max(case when Specifications='Depth (mm)' then value end) as Depthmm, max(case when Specifications='Grooves' then value end) as Grooves, max(case when Specifications='Height (mm)' then value end) as Height, max(case when Specifications='Inlet (mm)' then value end) as Inlet, max(case when Specifications='Belt Width (_)' then value end) as belt from table_name group by Partnumber