Pivot query using SQL Server

Question

I need a dynamic PIVOT query using SQL Server.

id year amount
1  1991  25
1  1992  24
2  1991  25
2  1992  24

How to change below table using pivot:

id  1991_amount     1992_amount
1   1991_25         1992_24
2   1991_25         1992_24

Answer 1

Here is my solution.

First if you don't need a dynamic one you can use this:

-- Create demo data
CREATE TABLE #data (id int, year int, amount int)

INSERT INTO #data(id, year, amount)
VALUES  (1,1991,25),
    (1,1992,24),
    (2,1991,25),
    (2,1992,24)

-- Your work:
SELECT pvt.*
FROM (
    SELECT id, CONVERT(nvarchar(max),year)+N'_amount' as year, 
            CONVERT(nvarchar(max),year)+N'_'+CONVERT(nvarchar(max),amount) as amount 
    FROM #data
) as dat
PIVOT (
    MAX(amount)
    FOR year IN([1991_amount],[1992_amount])
) as pvt

-- Cleanup
DROP TABLE #data
GO

If you really need a dynamic one you can use this one instead:

-- Create demo data
CREATE TABLE #data (id int, year int, amount int)

INSERT INTO #data(id, year, amount)
VALUES  (1,1991,25),
    (1,1992,24),
    (2,1991,25),
    (2,1992,24)

-- Your work:
DECLARE @sql nvarchar(max), @columns nvarchar(max)

SELECT @columns = COALESCE(
            @columns + N',['+ CONVERT(nvarchar(max),year) + N'_amount]', N'['+
            CONVERT(nvarchar(max),year) + N'_amount]'
       )
FROM (
    -- Distinct do avoid duplicated year columns
    SELECT DISTINCT year
    FROM #data
) as dat

SET @sql = N'
SELECT pvt.*
FROM (
    SELECT id, CONVERT(nvarchar(max),year)+N''_amount'' as year, 
        CONVERT(nvarchar(max),year)+N''_''+ CONVERT(nvarchar(max),amount) as amount 
    FROM #data
) as dat
PIVOT (
    MAX(amount)
    FOR year IN('+@columns+')
) as pvt'
EXEC(@sql)

-- Cleanup
DROP TABLE #data

Just a small addition. If you may have multiple amounts for a group containing id and year, you can replace the subquery or dat with this one to use SUM and sum them up.

In this case you replace this:

FROM (
    SELECT id, CONVERT(nvarchar(max),year)+N'_amount' as year, 
            CONVERT(nvarchar(max),year)+N'_'+CONVERT(nvarchar(max),amount) as amount 
    FROM #data
) as dat

with this:

FROM (
    SELECT id, CONVERT(nvarchar(max),year)+N'_amount' as year, 
            CONVERT(nvarchar(max),year)+N'_'+CONVERT(nvarchar(max),SUM(amount)) as amount 
    FROM #data
    GROUP BY id, year
) as dat

It will provide the following result set:

id          1991_amount  1992_amount
----------- ------------ ------------
1           1991_25      1992_24
2           1991_25      1992_24