Transposing groups of rows with the same ID into another table

Question

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://i.stack.imgur.com/fnQAn.jpg

This is what I want it to look like (table: Demo2): [2]: https://i.stack.imgur.com/i2DZE.jpg

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;

Consider handling issues of data display in application code — Strawberry, Jan 09 '19 at 20:51

Caius Jard · Answer 1 · 2019-01-09T20:46:56.377

This is indeed a pivot, also called a crosstab, or sometimes transpose

Some databases have dedicated facilities for doing it, others you have to use a grouping syntax. I prefer the latter because it works universally

If it's any consolation, you were really close!

SELECT
    DIM_KEY,
    MAX(CASE WHEN ATTR_NAME = 'UPC' THEN VALUE END) as UPC,
    MAX(CASE WHEN ATTR_NAME = 'DAIRY_CLM' THEN VALUE END) as DAIRY_CLM,
    MAX(CASE WHEN ATTR_NAME = 'KOSHER_CLM' THEN VALUE END) as KOSHER_CLM,
    MAX(CASE WHEN ATTR_NAME = 'FAT' THEN VALUE END) as FAT,
    MAX(CASE WHEN ATTR_NAME = 'CALORIES' THEN VALUE END) as CALORIES
FROM demo
GROUP BY DIM_KEY

How does it work?

Well, if you run the non-grouped, no-max-functions version that you already had:

SELECT
    DIM_KEY,
    (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
   demo

Then you'll see your data become "diagonal":

3005, 123423, null, null...
3005, null,   N,    null...
3005, null,   null, Y   ...

In each column (per dim_key) there is only one value, the rest are NULL

Adding in the GROUP BY and MAX causes these to collapse into a single row because MAX() will return just the value from the column and make all the nulls disappear. It's an intrinsic property of a grouping, that the row data doesnt "stay together" - within the group of a particular DIM_KEY, the MAX(DAIRY_CLM) can come from any row, the MAX(KOSHER_CLM) can come from any other row.. In practice this means that the single values are picked, the nulls are discarded, they all appear on the same row..

..and thus your vertical data went horizontal, after going through the diagonal

score 0 · Answer 2 · answered Jan 09 '19 at 20:38

You could use a (fake) aggregation function and group by

INSERT INTO Demo2 (UPC, DAIRY_CLM, KOSHER_CLM, FAT, CALORIES)
SELECT min(CASE WHEN ATTR_NAME = 'UPC' THEN VALUE END) ,
      min(CASE WHEN ATTR_NAME = 'DAIRY_CLM' THEN VALUE END),
     min(CASE WHEN ATTR_NAME = 'KOSHER_CLM' THEN VALUE END),
    min(CASE WHEN ATTR_NAME = 'FAT' THEN VALUE END),
    min(CASE WHEN ATTR_NAME = 'CALORIES' THEN VALUE END)
FROM Demo1
group by DIM_KEY;

this should work in the same way for both the db ..

Transposing groups of rows with the same ID into another table

2 Answers2

Linked