Pivot-Type Action

Question

I have a schema that has a few tables. See below example schema and data set

CREATE TABLE stk_info (
    TypeId int not null auto_increment,
    Primary Key (TypeId),
    TypeName varchar(50) not null
);
INSERT INTO stk_info (TypeName) VALUES ('Type 1'), ('Type 2');
CREATE TABLE stk_item (
    ItemId int not null auto_increment,
    primary key (ItemId),
    ItemName varchar(50) not null
);
INSERT INTO stk_item (ItemName) VALUES ('Item A');
CREATE TABLE stk_item_attributes (
    AttributeId int not null auto_increment,
    primary key (AttributeId),
    ItemId int not null,
    Foreign Key (ItemId) references stk_item (ItemId),
    TypeId int not null,
    Foreign Key (TypeId) references stk_info (TypeId),
    AttributeValue varchar(50)
);
INSERT INTO stk_item_attributes (ItemId, TypeId, AttributeValue)
VALUES (1, 1, 'Type 1 Value'), (1, 2, 'Type 2 Value');
SELECT si.*, s.TypeName, sia.AttributeValue
from stk_item si
LEFT JOIN stk_item_attributes sia on si.ItemId = sia.ItemId
LEFT JOIN stk_info s on sia.TypeId = s.TypeId

With the data returned from the query looking like:

How can I transform the above data to look like:

ItemId | ItemName | Type 1       | Type 2
1      | Item A   | Type 1 Value | Type 2 Value

I tried a big case

SELECT si.*,
       case when s.TypeId = 1 then sia.AttributeValue end as 'Type 1',
       case when s.TypeId = 2 then sia.AttributeValue end as 'Type 2'
from stk_item si
LEFT JOIN stk_item_attributes sia on si.ItemId = sia.ItemId
LEFT JOIN stk_info s on sia.TypeId = s.TypeId
GROUP BY si.ItemId, si.ItemName;

Which returns a null type 2 value

I removed the "Group By" in the above query then got two rows with expected results in their place, but not merged:

I am not sure where to go from here!

Jay McEh · Answer 1 · 2024-01-03T18:35:14.400

If your goal is one row per ItemID, your Attributes need to be aggregated. If you might have multiple Type2 Attributes for a given ItemID, then you presumably want a SUM (or AVG, or a concatenated delimited string-- much depends on specific requirements not present in the question).

If you will only ever have a single Attribute of a given type for any given ItemID, then MAX is likely your better option:

SELECT si.*,
       MAX(case when s.TypeId = 1 then sia.AttributeValue end) as 'Type 1',
       MAX(case when s.TypeId = 2 then sia.AttributeValue end) as 'Type 2'
from stk_item si
LEFT JOIN stk_item_attributes sia on si.ItemId = sia.ItemId
LEFT JOIN stk_info s on sia.TypeId = s.TypeId
GROUP BY si.ItemId, si.ItemName;

The basic rule to keep in mind is that if you have a GROUP BY, every field in your SELECT should either be an aggregate function or present in your GROUP BY. There are exceptions, but it's a good general rule and one of many reasons to avoid SELECT * outside of simple examples and ad-hoc testing.

Pivot-Type Action

1 Answers1