How to group by records based on year in SQL server

Question

I have a table called Product which display the data as below:

---------------------------------
l  TYPE   l PERIOD l  BALANCE   l
l   Auto  l  2015  l     5      l     
l   Car   l  2015  l     2      l     
l   Bus   l  2015  l     45     l   
l   Auto  l  2016  l     2      l     
l   Car   l  2016  l     6      l     
l   Bus   l  2016  l     50     l  
l   Auto  l  2017  l     12     l     
l   Car   l  2017  l     16     l     
l   Bus   l  2017  l     10     l  
---------------------------------

I need to query the this table so that the result is based on the year similar to below:

---------------------------------------------------------
l TYPE    l BALANCE 2015 l BALANCE 2016 l BALANCE 2017 l
l   Auto  l     5        l       2      l     12       l
l   Car   l     2        l       6      l     16       l
l   Bus   l     45       l       50     l     10       l
---------------------------------------------------------

I have tried different queries but no luck.

Your help will be very useful,

Thanks in Advance.

MySQL or MS SQL Server? (Different products, and the answer might be product specific.) — jarlh, Mar 15 '16 at 10:02
If a 2014 row suddenly is inserted, will it be included in the result somehow? — jarlh, Mar 15 '16 at 10:03

score 4 · Answer 1 · answered Mar 15 '16 at 09:51

4

You can use conditional aggregation like this:

SELECT t.type,
       MAX(CASE WHEN period = 2015 then balance end) as BALANCE_2015,
       MAX(CASE WHEN period = 2016 then balance end) as BALANCE_2016,
       MAX(CASE WHEN period = 2017 then balance end) as BALANCE_2017
FROM Product t
GROUP BY t.type

You can add more columns depends on the years you want to display.

answered Mar 15 '16 at 09:51

sagi

40,026
6
59
84

Thanks for the query , but i am not sure about how many years the column "PERIOD" will have so i cant hard code the query with period 2015,2016,2017. is this query can be modified to pick all the year dynamically from the table – Mar 15 '16 at 11:24

score 1 · Answer 2 · answered Mar 15 '16 at 10:08

1

SELECT *
FROM (
    SELECT period, type, balance 
    FROM dbo.Product
) t
PIVOT (
    MAX(balance)
    FOR period IN ([2015], [2016], [2017]) 
) p

answered Mar 15 '16 at 10:08

Devart

119,203
23
166
186

score 1 · Answer 3 · answered Mar 15 '16 at 10:08

You can try using PIVOT

select [Type],[2015] as BALANCE2015,[2016] as BALANce2016,[2017] as BALANCe2017 from 
(
select [Type],[Period],[Balance] from my_table
) as t
pivot 
(
max([Balance]) for Period in ([2015],[2016],[2017])
) as pvt
order by pvt.[Type]

Sooraj · Answer 4 · 2016-03-15T13:12:22.537

For your scenario, since the "Period" is a table column which can have any value, the above method of hardcoding values won't be a good idea.

You should generate a dynamic PIVOT query based on the values of Period.

Checkout the following link.

http://www.codeproject.com/Tips/1011591/Dynamic-Pivot-Query-in-SQL-Server

Update :

Following query is what you need.

DECLARE @ColumnName AS VARCHAR(MAX)
DECLARE @DynamicPivotQuery AS VARCHAR(MAX) = ''

SELECT @ColumnName = COALESCE(@ColumnName + ',', '') + QUOTENAME(PERIOD)
        FROM (SELECT DISTINCT PERIOD FROM Product) T;

SET @DynamicPivotQuery = N'SELECT [TYPE], ' + @ColumnName + ' FROM Product ' + 
                        ' PIVOT(SUM([BALANCE]) FOR PERIOD IN (' + @ColumnName + ')) AS PVTTable;';

EXEC (@DynamicPivotQuery)

Note:

I have used SUM as aggregate function in PIVOT, you can change it to MAX or MIN as per your requirement.

How to group by records based on year in SQL server

4 Answers4