March 25, 2015 at 6:31 am
Hello Team -
Is there a way to show multiple metrics in 1 SQL pivot operator. Basically, I have the Table1 and want the desired results is the Table2 format.
Table1
ACCOUNTS YEARREVENUEMARGIN
ACCOUNT1 2012100 50
ACCOUNT1 2013104 52
ACCOUNT1 2014108 54
ACCOUNT2 2012112 56
ACCOUNT2 2013116 58
ACCOUNT2 2014120 60
ACCOUNT3 2012124 62
ACCOUNT3 2013128 64
ACCOUNT3 2014132 66
Table2
ACCOUNTSREVENUE_2012REVENUE_2013REVENUE_2014MARGIN_2012MARGIN_2013MARGIN_2014
ACCOUNT1 100 104 108 50 52 54
ACCOUNT2 112 116 120 56 58 60
ACCOUNT3 124 128 132 62 64 66
Pleae help
March 25, 2015 at 9:15 am
Please post table DDL and sample data in the form of INSERT statements next time.
It helps the people to help you, and you don't have to wait too long for an answer.
WITH CTE_TestData AS
(
SELECT ACCOUNTS= 'ACCOUNT1', [YEAR] = 2012, REVENUE = 100, MARGIN = 50
UNION ALL
SELECT ACCOUNTS= 'ACCOUNT1', [YEAR] = 2013, REVENUE = 104, MARGIN = 52
UNION ALL
SELECT ACCOUNTS= 'ACCOUNT1', [YEAR] = 2014, REVENUE = 108, MARGIN = 54
UNION ALL
SELECT ACCOUNTS= 'ACCOUNT2', [YEAR] = 2012, REVENUE = 112, MARGIN = 56
UNION ALL
SELECT ACCOUNTS= 'ACCOUNT2', [YEAR] = 2013, REVENUE = 116, MARGIN = 58
UNION ALL
SELECT ACCOUNTS= 'ACCOUNT2', [YEAR] = 2014, REVENUE = 120, MARGIN = 60
UNION ALL
SELECT ACCOUNTS= 'ACCOUNT3', [YEAR] = 2012, REVENUE = 124, MARGIN = 62
UNION ALL
SELECT ACCOUNTS= 'ACCOUNT3', [YEAR] = 2013, REVENUE = 128, MARGIN = 64
UNION ALL
SELECT ACCOUNTS= 'ACCOUNT3', [YEAR] = 2014, REVENUE = 132, MARGIN = 66
)
SELECT
ACCOUNTS
,REVENUE_2012= MAX(IIF([YEAR] = 2012,REVENUE,NULL))
,REVENUE_2013= MAX(IIF([YEAR] = 2013,REVENUE,NULL))
,REVENUE_2014= MAX(IIF([YEAR] = 2014,REVENUE,NULL))
,MARGIN_2012= MAX(IIF([YEAR] = 2012,MARGIN,NULL))
,MARGIN_2013= MAX(IIF([YEAR] = 2013,MARGIN,NULL))
,MARGIN_2014= MAX(IIF([YEAR] = 2014,MARGIN,NULL))
FROM CTE_TestData
GROUP BY ACCOUNTS;
Have fun updating that SQL statement every year 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 25, 2015 at 9:23 am
Koen Verbeeck (3/25/2015)
Have fun updating that SQL statement every year 😉
Or create a dynamic version 🙂
March 26, 2015 at 9:06 am
Couple ways to go about this...I always find dynamic sql fun, so thought I'd share.
Also, I can imagine you might only want to show a few years at a time or it might get unruly, but if you need to add more you can do that easily.
declare @min_year as int
, @sql as nvarchar(max)
select @min_year = 2012
set @sql =
'select acc.accounts
, case when yr1.revenue is not null then yr1.revenue end as ''' + cast(@min_year as char(4)) + '_revenue''
, case when yr1.margin is not null then yr1.margin end as ''' + cast(@min_year as char(4)) + '_margin''
, case when yr2.revenue is not null then yr2.revenue end as ''' + cast(@min_year+1 as char(4)) + '_revenue''
, case when yr2.margin is not null then yr2.margin end as ''' + cast(@min_year+1 as char(4)) + '_margin''
, case when yr3.revenue is not null then yr3.revenue end as ''' + cast(@min_year+2 as char(4)) + '_revenue''
, case when yr3.margin is not null then yr3.margin end as ''' + cast(@min_year+2 as char(4)) + '_margin''
from (SELECT DISTINCT ACCOUNTS FROM Table1 ) ACC
left join (select accounts
, Revenue
, Margin
from Table1
where year = ''' + cast(@min_year as char(4)) + ''') yr1
on acc.accounts = yr1.accounts'
+ '
left join (
select accounts
, Revenue
, Margin
from Table1
where year = ''' + cast(@min_year+1 as char(4)) + ''') yr2
on acc.accounts = yr2.accounts'
+ '
left join (
select accounts
, Revenue
, Margin
from Table1
where year = ''' + cast(@min_year+2 as char(4)) + ''') yr3
on acc.accounts = yr3.accounts'
--print @sql
execute (@sql);
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply