SQL Pivot data into one row per item

Question

I have a table like this, with product name specified multiple times for each LAY it has:

  NAME   |  LAYER  |  TYPE  |  DEPTH
-------------------------------------
32_42_1  | LAY_1   |   A    |  99.4
32_42_1  | LAY_2   |   D    |  427.2
32_42_1  | LAY_3   |   X    |  120.4
32_42_1  | LAY_4   |   B    |  27
32_42_2  | LAY_1   |   A    |  150.4
32_42_2  | LAY_2   |   D    |  427.2
32_42_2  | LAY_3   |   X    |  121.4
32_42_2  | LAY_4   |   C    |  40
32_42_3  | LAY_1   |   F    |  80.97
32_42_3  | LAY_2   |   Y    |  300.2
32_42_3  | LAY_3   |   C    |  123.4
32_42_3  | LAY_4   |   C    |  120

I need each NAME to only have one row and so it needs to be rotated like this:

 NAME  | LAY_1_TYPE | LAY_1_DEPTH | LAY_2_TYPE | LAY_2_DEPTH | LAY_3...
--------------------------------------------------------------------
32_42_1|    A       |    99.4     |      D     |   427.2     | ...
32_42_2|    A       |    150.4    |      D     |   427.2     | ...
32_42_3|    F       |    80.7     |      Y     |   300.2     | ...

I have found many similar solutions, and while some of them get close, I haven't been able to change the code to suit my needs.

This is what I have so far:

PIVOT
(
    MAX(TYPE) For LAYER In (LAY_1,LAY_2,LAY_3,LAY_4,LAY_5)
) piv

But this only pivots one column, and doesn't condense the data into one row per "Name"

Any help would be appreciated!

score 1 · Accepted Answer · answered May 02 '18 at 23:45

Pivot with more than one column is better done with conditional aggregation:

DECLARE @tbl TABLE (NAME VARCHAR(100),LAYER VARCHAR(100),TYPE VARCHAR(100),DEPTH DECIMAL(10,4));
INSERT INTO @tbl VALUES
 ('32_42_1','LAY_1','A','99.4')
,('32_42_1','LAY_2','D','427.2')
,('32_42_1','LAY_3','X','120.4')
,('32_42_1','LAY_4','B','27')
,('32_42_2','LAY_1','A','150.4')
,('32_42_2','LAY_2','D','427.2')
,('32_42_2','LAY_3','X','121.4')
,('32_42_2','LAY_4','C','40')
,('32_42_3','LAY_1','F','80.97')
,('32_42_3','LAY_2','Y','300.2')
,('32_42_3','LAY_3','C','123.4')
,('32_42_3','LAY_4','C','120');

SELECT t.NAME
      ,MAX(CASE WHEN t.LAYER='LAY_1' THEN t.TYPE END) AS Type1
      ,MAX(CASE WHEN t.LAYER='LAY_1' THEN t.DEPTH END) AS Depth1
      ,MAX(CASE WHEN t.LAYER='LAY_2' THEN t.TYPE END) AS Type2
      ,MAX(CASE WHEN t.LAYER='LAY_2' THEN t.DEPTH END) AS Depth2
      ,MAX(CASE WHEN t.LAYER='LAY_3' THEN t.TYPE END) AS Type3
      ,MAX(CASE WHEN t.LAYER='LAY_3' THEN t.DEPTH END) AS Depth3
      ,MAX(CASE WHEN t.LAYER='LAY_4' THEN t.TYPE END) AS Type4
      ,MAX(CASE WHEN t.LAYER='LAY_4' THEN t.DEPTH END) AS Depth4
FROM @tbl AS t
GROUP BY t.NAME;

The result

+---------+-------+----------+-------+----------+-------+----------+-------+----------+
| NAME    | Type1 | Depth1   | Type2 | Depth2   | Type3 | Depth3   | Type4 | Depth4   |
+---------+-------+----------+-------+----------+-------+----------+-------+----------+
| 32_42_1 | A     | 99.4000  | D     | 427.2000 | X     | 120.4000 | B     | 27.0000  |
+---------+-------+----------+-------+----------+-------+----------+-------+----------+
| 32_42_2 | A     | 150.4000 | D     | 427.2000 | X     | 121.4000 | C     | 40.0000  |
+---------+-------+----------+-------+----------+-------+----------+-------+----------+
| 32_42_3 | F     | 80.9700  | Y     | 300.2000 | C     | 123.4000 | C     | 120.0000 |
+---------+-------+----------+-------+----------+-------+----------+-------+----------+

SQL Pivot data into one row per item

1 Answers1