How to pivot SQL table without aggregate data

Question

I'm trying to pivot my table:

CREATE TABLE articles (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
HCode varchar(2) ,
Style varchar(4) ,
Color varchar(3) ,
Layer smallint ,
HEX varchar(6) 
);

with some sample values

INSERT INTO articles(HCode, Style, Color, Layer, HEX) 
VALUES
('01', '0002', '001', '1', 'FFFFFF'),
('01', '0002', '002', '1', '000000'),
('01', '0002', '003', '1', '002A5A'),
('02', '0568', '586', '1', '11906C'),
('02', '0568', '586', '2', 'FFFFFF'),
('02', '0596', 'i91', '1', '009F48'),
('02', '0596', 'i91', '2', '245329'),
('02', '0640', 's23', '1', 'AEAB9C'),
('02', '0640', 's23', '2', '4C565C'),
('02', '0640', 's23', '3', 'BC111E');

Below a pic how the table is constructed.

to:

| HCode | Style | Color | Layer | hex1 | hex2 | hex3 |
|-------+-------+-------+-------+------+------+------|
|   01  |  0002 |  001  |   1   |FFFFFF| NULL | NULL |
|   01  |  0002 |  002  |   1   |000000| NULL | NULL |
|   01  |  0002 |  003  |   1   |002A5A| NULL | NULL |
|   02  |  0568 |  586  |   2   |11906C|FFFFFF| NULL |

If there the layer > 1 the articles have two colors. The max of layers is 3.

I thought that i can use a pivot table to transposing my table with additional logic to split the hex into 3 columns.

WITH pivot_data AS
(
    SELECT * FROM articles
)

SELECT * FROM pivot_data
PIVOT(
    MAX(CONCAT(HCode,Style,Color)) FOR HEX IN ([hex1], [hex2], [hex3])
) AS PVT

my problem is how do i aggregate 3 columns ? MAX(CONCAT(HCode,Style,Color))is unfortunately not working.

Are you using SQL Server? The `AUTO_INCREMENT` and the backticks syntax seems like MySQL. — EzLo, Apr 30 '19 at 08:34
Hi, yes im using mssql. You are right i've chosen the wrong syntax. I will update my question — Greg Ostry, Apr 30 '19 at 08:37

score 1 · Accepted Answer · answered Apr 30 '19 at 08:33

If you have a limited and known amount of layers (and not many), an alternative to pivoting is conditional aggregation. This means using GROUP BY with aggregate functions that work with CASE clauses.

SELECT
    A.HCode,
    A.Style,
    A.Color,
    MAX(A.Layer) Layer,
    MAX(CASE WHEN A.Layer = 1 THEN A.HEX END) Hex1,
    MAX(CASE WHEN A.Layer = 2 THEN A.HEX END) Hex2,
    MAX(CASE WHEN A.Layer = 3 THEN A.HEX END) Hex3
FROM
    articles A
GROUP BY
    A.HCode,
    A.Style,
    A.Color

Result:

HCode   Style   Color   Layer   Hex1    Hex2    Hex3
01      0002    001     1       FFFFFF  NULL    NULL
01      0002    002     1       000000  NULL    NULL
01      0002    003     1       002A5A  NULL    NULL
02      0568    586     2       11906C  FFFFFF  NULL
02      0596    i91     2       009F48  245329  NULL
02      0640    s23     3       AEAB9C  4C565C  BC111E

Okey my idea using pivot for this solution was very confusing. Your answer solves very quickly my problem because i have only max 3 layers. Thank you ! — Greg Ostry, Apr 30 '19 at 08:44

How to pivot SQL table without aggregate data

1 Answers1