How to convert unknown values in column to row in SQL Server

Question

How to convert unknown values in column to row with fixed column and sum value in another column in SQL Server?

This example

   | Id | Date | Amount1 | Amount2
   +----+------+---------+--------
   | 1  | 2018 | 1000    | 100
   | 2  | 2018 | 2000    | 200
   | 2  | 2018 | 3000    | 300
   | 1  | 2019 | 4000    | 400
   | 1  | 2019 | 5000    | 500
   | .. | .... | ....    | ...

I want this

| Id | 2018Amount1 | 2018Amount2 | 2019Amount1 | 2019Amount2 | ...
+----+-------------+-------------+-------------+-------------+-------
| 1  |    1000     |     100     |   9000      |   900       | ...
| 2  |    5000     |     500     |   0         |   0         | ...

score 1 · Answer 1 · edited Mar 05 '19 at 11:45

1

Use conditional aggregation :

select Id, 
       sum( case when Date = 2018 then Amount1 end ) as "2018Amount1",
       sum( case when Date = 2018 then Amount2 end ) as "2018Amount2",
       sum( case when Date = 2019 then Amount1 end ) as "2019Amount1",
       sum( case when Date = 2019 then Amount2 end ) as "2019Amount2" 
  from tab
 group by Id

edited Mar 05 '19 at 11:45

Gordon Linoff

1,198,228
53
572
709

answered Mar 05 '19 at 11:15

Barbaros Özhan

47,993
9
26
51

but values are unkown – super.Omar Mar 05 '19 at 11:17
Barbaros Özhan thanks but I think pivot make this – super.Omar Mar 05 '19 at 11:37
@super.Omar welcome Omar, yes we have a method using `pivot` keyword but also that needs explicit specification for all of the returning columns. – Barbaros Özhan Mar 05 '19 at 11:39
@super.Omar Larnu suggests a better, dynamical way. – Barbaros Özhan Mar 05 '19 at 11:41
**The basis of the problem I have this** | Id | Date | Amount1 +----+------+-------- | 1 | 2018 | 3000 | 1 | 2019 | -3000 | 1 | 2019 | 3000 | .. | .... | .... **How I Get this** | Id | Date2018 | Date2019 +----+------+-------- | 1 | 3000 | 0 | .. | .... | .... **I don't need this** | Id | Date2018 | Date2019 +----+------+-------- | 1 | 0 | 3000 | .. | .... | .... – super.Omar Mar 05 '19 at 11:47
@Gordon Linoff thank you master, I confused the phrases :) – Barbaros Özhan Mar 05 '19 at 11:48

score 1 · Accepted Answer · answered Mar 05 '19 at 12:06

You can use dynamic pivot

DECLARE @ColName NVARCHAR(MAX) =''
SELECT @ColName = @ColName + ', '+ ColName
FROM (  SELECT  DISTINCT  QUOTENAME( CAST([Date] AS VARCHAR(4)) + 'Amount1') 
            + ', ' + QUOTENAME(CAST([Date] AS VARCHAR(4)) + 'Amount2') ColName FROM TestTable )T

SET @ColName = STUFF (@ColName,1,1,'')

DECLARE @SqlText NVARCHAR(MAX) = 
'SELECT * FROM (
    SELECT Id, CAST([Date] AS VARCHAR(4)) + ''Amount1'' AS [Date], Amount1 AS Amount FROM TestTable
    UNION 
    SELECT Id, CAST([Date] AS VARCHAR(4)) + ''Amount2'' [Date], Amount2 AS Amount FROM TestTable
) SRC
PIVOT(SUM(Amount) FOR Date IN (' + @ColName+ ') ) AS PVT'

EXECUTE sp_executesql @SqlText

How to convert unknown values in column to row in SQL Server

2 Answers2