I have a table, Demo1, where I have multiple similar DIM_KEY's for several different ATTR_NAME's (attribute names). I want to display a table with columns for DIM_KEY, UPC, DAIRY_CLM, KOSHER_CLM, FAT, and CALORIES where the matching values are the rows.
This is the original table Demo1: [1]: https://imgur.com/a/KqayM1C
This is what I want it to look like (table: Demo2): [2]: https://imgur.com/a/nwpoHhv
I've tried inserting the rows from Demo1 into an empty Demo2 table but that didn't work out like I wanted it to. I also couldn't get the DIM_KEY in that way. I also tried using PIVOT but got nothing but errors. I am using MySQL but this also needs to work in SSMS. Preferibly SSMS if I had to choose one.
INSERT INTO Demo2 (UPC, DAIRY_CLM, KOSHER_CLM, FAT, CALORIES)
SELECT
(CASE WHEN ATTR_NAME = 'UPC' THEN VALUE END),
(CASE WHEN ATTR_NAME = 'DAIRY_CLM' THEN VALUE END),
(CASE WHEN ATTR_NAME = 'KOSHER_CLM' THEN VALUE END),
(CASE WHEN ATTR_NAME = 'FAT' THEN VALUE END),
(CASE WHEN ATTR_NAME = 'CALORIES' THEN VALUE END)
FROM Demo1;
See Question&Answers more detail:os