I am trying to transpose rows into columns and concatenate type with header to get one line.
My table:
| type | name | num | age | dateA | DateB | PRICE |
|---|---|---|---|---|---|---|
| A | Aziz | 1 | 12 | 2012-01-11 00:00:00.000 | 2012-01-11 00:00:00.000 | 129.00 |
| B | Aziz | 1 | 34 | 2012-01-11 00:00:00.000 | 2012-01-11 00:00:00.000 | 100.00 |
| C | Aziz | 0 | 20 | 2012-01-11 00:00:00.000 | 2012-01-11 00:00:00.000 | 349.00 |
| D | Aziz | 1 | 90 | 2012-01-11 00:00:00.000 | 2012-01-11 00:00:00.000 | 222.00 |
The result I'd like to have:
| name | num x A | age x A | dateA x A | DateB x A | PRICE x A | num x B | age x B | dateA x B | DateB x B | PRICE x B |
|---|---|---|---|---|---|---|---|---|---|---|
| Aziz | 1 | 12 | 2012-01-11 | 2012-01-11 | 129.00 | 1 | 34 | 2012-01-11 | 2012-01-11 | 100.00 |
the same thing with C and D all in one line
CodePudding user response：
You can do this the long way like this:
SELECT name,
[num x A] = MAX(CASE type WHEN 'A' THEN num END),
[age x A] = MAX(CASE type WHEN 'A' THEN age END),
[dateA x A] = MAX(CASE type WHEN 'A' THEN dateA END),
[DateB x A] = MAX(CASE type WHEN 'A' THEN DateB END),
[PRICE x A] = MAX(CASE type WHEN 'A' THEN PRICE END),
[num x B] = MAX(CASE type WHEN 'B' THEN num END),
[age x B] = MAX(CASE type WHEN 'B' THEN age END),
[dateA x B] = MAX(CASE type WHEN 'B' THEN dateA END),
[DateB x B] = MAX(CASE type WHEN 'B' THEN DateB END),
[PRICE x B] = MAX(CASE type WHEN 'B' THEN PRICE END),
[num x C] = MAX(CASE type WHEN 'C' THEN num END),
[age x C] = MAX(CASE type WHEN 'C' THEN age END),
[dateA x C] = MAX(CASE type WHEN 'C' THEN dateA END),
[DateB x C] = MAX(CASE type WHEN 'C' THEN DateB END),
[PRICE x C] = MAX(CASE type WHEN 'C' THEN PRICE END),
[num x D] = MAX(CASE type WHEN 'D' THEN num END),
[age x D] = MAX(CASE type WHEN 'D' THEN age END),
[dateA x D] = MAX(CASE type WHEN 'D' THEN dateA END),
[DateB x D] = MAX(CASE type WHEN 'D' THEN DateB END),
[PRICE x D] = MAX(CASE type WHEN 'D' THEN PRICE END)
FROM dbo.YourTableName GROUP BY name;
Working example in this fiddle.
You can also do this dynamically, which will help if there are more types added later, or the schema changes:
DECLARE @sql nvarchar(max),
@types varchar(32) = 'A,B,C,D',
@cols nvarchar(max) = N'num,age,dateA,DateB,PRICE';
SELECT @sql = STRING_AGG(o, ',' char(13) char(10)) FROM
(
SELECT QUOTENAME(CONCAT(c.value, ' x ', t.value))
CONCAT(' = MAX(CASE type WHEN ',
QUOTENAME(t.value, char(39)), N' THEN ', c.value, N' END)')
FROM STRING_SPLIT(@types, ',') AS t
CROSS APPLY STRING_SPLIT(@cols, ',') AS c
) AS x(o);
SET @sql = N'SELECT name,
' @sql N' FROM dbo.YourTableName GROUP BY name;';
EXECUTE sys.sp_executesql @sql;
That is demonstrated in this fiddle.