CODEWITHSUNDEEP
sql-serverpivot
Greg Ostry
Greg Ostry

Reputation: 1231

How to pivot SQL table without aggregate data

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.

enter image description here

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.

Upvotes: 2

Views: 37

Answers (1)

EzLo
EzLo

Reputation: 14189

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

Upvotes: 1

Related Questions