Pivot table for report
-
I have table where I want the categories to be the headers and entity, and channel to be the rows reporting the charges. The category
must be dynamic as more will be added to the table .
Thanks,
example output
channel entity cat1 cat2 cat3
2a 88 0 50 100
2a 99 10 0 50
CREATE TABLE #InvoicePaid
(
tmp_channel NVARCHAR(10)
,tmp_entity NVARCHAR(10)
,tmp_category NVARCHAR(25)
,tmp_charges DEC(18,2)
)
; -
Not dynamic
select tmp_channel, tmp_entity,
sum(case when tmp_category='Cat1' then tmp_charges else 0 end) cat1,
sum(case when tmp_category='Cat2' then tmp_charges else 0 end) cat2,
sum(case when tmp_category='Cat3' then tmp_charges else 0 end) cat3
from #InvoicePaid
group by tmp_channel, tmp_entity
order by tmp_channel, tmp_entity;Dynamic (before 2017)
declare
@sql_prefix nvarchar(max)='select tmp_channel, tmp_entity,',
@sql_suffix nvarchar(max)=' from #InvoicePaid group by tmp_channel, tmp_entity order by tmp_channel, tmp_entity;',
@line_prefix nvarchar(max)=' sum(case when tmp_category=''',
@line_suffix nvarchar(max)=''' then tmp_charges else 0 end) ',
@sql nvarchar(max);
with
cats_cte(tmp_category) as (
select distinct tmp_category
from #InvoicePaid),
pvt_cte(sql_string) as (
select stuff((select concat(', ', @line_prefix, tmp_category,
@line_suffix, tmp_category)
from cats_cte
order by tmp_category
for xml path('')),1,1,'')
from cats_cte)
select @sql=concat(@sql_prefix, sql_string, @sql_suffix)
from pvt_cte;
--print(@sql);
exec sp_sqlexec @sql;Dynamic (after 2017)
declare
@sql_prefix nvarchar(max)='select tmp_channel, tmp_entity,',
@sql_suffix nvarchar(max)=' from #InvoicePaid group by tmp_channel, tmp_entity order by tmp_channel, tmp_entity;',
@line_prefix nvarchar(max)=' sum(case when tmp_category=''',
@line_suffix nvarchar(max)=''' then tmp_charges else 0 end) ',
@sql nvarchar(max);
with
cats_cte(tmp_category) as (
select distinct tmp_category
from #InvoicePaid),
pvt_cte(sql_string) as (
select string_agg(concat(@line_prefix, tmp_category, @line_suffix, tmp_category), ',')
within group (order by tmp_category)
from cats_cte)
select @sql=concat(@sql_prefix, sql_string, @sql_suffix)
from pvt_cte;
--print(@sql);
exec sp_sqlexec @sql;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Would that code change if SQL2012 ?
Thanks for reply
-
Yes, no STRING_AGG in 2012. We're in the 2016 Forum 🙂 To revert you could use STUFF+XML instead of STRING_AGG.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Not too long ago there was a question for SQL Server 2012 with pretty much the same pattern.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Are you using SSRS for the report - or some other reporting tool? If so - then don't pivot the data in SQL Server, let SSRS (or other reporting tool) pivot the data using a matrix object instead. It will much more flexible and much easier to manage and maintain.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Steve Collins wrote:
Yes, no STRING_AGG in 2012. We're in the 2016 Forum 🙂 To revert you could use STUFF+XML instead of STRING_AGG.
Doesn't matter if it is a 2016 forum... I'm pretty sure that STRING_AGG isn't available until 2017. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Oops (again). Thank you Jeff. I updated the answer with before/after 2017.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- Steve Collins wrote:
Oops (again). Thank you Jeff. I updated the answer with before/after 2017.
Heh... thanks for the feedback, Steve. The reason I'm so very familiar with this one is that we currently have SQL Server 2016 where I work and, because of a lot of the types of work we do, I'd kill to have STRING_AGG() available in 2016! 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I would just be using SQL to drive out this report possibly email to a group.
THanks
-
On the pre-2017 is there any way to generate a Total record for each category?
Thanks for all replies..
-
If you add GROUPING SETS the non dynamic code looks something like this. Since most people want the total to appear on the bottom row, 2 grouping columns were added to the ORDER BY (and SELECT list).
select grouping(tmp_channel) grouping_channel,
grouping(tmp_entity) grouping_entity,
tmp_channel, tmp_entity,
sum(case when tmp_category='Cat1' then tmp_charges else 0 end) cat1,
sum(case when tmp_category='Cat2' then tmp_charges else 0 end) cat2,
sum(case when tmp_category='Cat3' then tmp_charges else 0 end) cat3
from #InvoicePaid
group by
grouping sets (
(tmp_channel, tmp_entity), ())
order by grouping_channel, grouping_entity, tmp_channel, tmp_entity;grouping_channelgrouping_entitytmp_channeltmp_entitycat1cat2cat3
002a880.0050.00100.00
002a9910.000.0050.00
11NULLNULL10.0050.00150.00Alternately, the GROUPING SETS columns could be added to the ORDER BY and not the SELECT list.
select tmp_channel, tmp_entity,
sum(case when tmp_category='Cat1' then tmp_charges else 0 end) cat1,
sum(case when tmp_category='Cat2' then tmp_charges else 0 end) cat2,
sum(case when tmp_category='Cat3' then tmp_charges else 0 end) cat3
from #InvoicePaid
group by
grouping sets (
(tmp_channel, tmp_entity), ())
order by grouping(tmp_channel), grouping(tmp_entity), tmp_channel, tmp_entity;tmp_channeltmp_entitycat1cat2cat3
2a880.0050.00100.00
2a9910.000.0050.00
NULLNULL10.0050.00150.00Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Let's do what Steve did (and nicely explained/demonstrated) but on steroids.
First, we need a very typical million rows to test against...
--===== Create the test table.
DROP TABLE IF EXISTS #InvoicePaid;
CREATE TABLE #InvoicePaid
(
tmp_channel NVARCHAR(10)
,tmp_entity NVARCHAR(10)
,tmp_category NVARCHAR(25)
,tmp_charges DEC(18,2)
)
;
--===== Populate the test table with a million row random, sometimes repetative, mix of
-- 99 different channels, 99 different entities, and 16 different categories
INSERT INTO #InvoicePaid WITH (TABLOCK)
(tmp_channel, tmp_entity, tmp_category, tmp_charges)
SELECT TOP 1000000
tmp_channel = ABS(CHECKSUM(NEWID())%99)+1
,tmp_entity = ABS(CHECKSUM(NEWID())%99)+1
,tmp_category = 'CAT'+RIGHT('000'+CONVERT(VARCHAR(10),ABS(CHECKSUM(NEWID())%16)+1),3)
,tmp_charges = RAND(CHECKSUM(NEWID()))*100
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;Next, we'll kind of do what Steve did except we'll add a "pre-aggregation" step to increase the performance by about 400%. We'll also label the sub-total rows for each channel and the grand total as well as having a total at the end of each row.
--===== Define the "main" SQL and the variable to accumulate column names in.
DECLARE @SQL NVARCHAR(MAX) = N'
WITH ctePreAgg AS
(--==== PreAggregate for performance
SELECT tmp_channel, tmp_entity, tmp_category, tmp_charges = SUM(tmp_charges)
FROM #InvoicePaid
GROUP BY tmp_channel, tmp_entity, tmp_category
)
--===== Do a "Black Arts" CROSSTAB to PIVOT the data with substotals and totals.
SELECT Channel = IIF(GROUPING(tmp_channel)=1, ''*** GRAND ***'',tmp_channel)
,Entity = IIF(GROUPING(tmp_entity)=1 , ''*** TOTAL ***'',tmp_entity)<<@Columns>>
,Total = SUM(tmp_charges)
FROM ctePreAgg
GROUP BY tmp_channel, tmp_entity WITH ROLLUP
ORDER BY GROUPING(tmp_channel),tmp_channel,GROUPING(tmp_entity),tmp_entity;'
,@Columns NVARCHAR(MAX) = ''
;
--===== Dynamically create the Category column dynamic SQL from the table data.
SELECT @Columns += NCHAR(10)+SPACE(8)+N','
+ tmp_category+' = SUM(IIF(tmp_category = N'''+tmp_category+''',tmp_charges,0))'
FROM #InvoicePaid
GROUP BY tmp_category
ORDER BY tmp_category
;
--===== Add the Category columns to the main dynamic SQL
SELECT @SQL = REPLACE(@SQL,N'<<@Columns>>',@Columns)
;
--===== Print, then execute the dynamic SQL.
PRINT @SQL;
EXEC (@SQL)
;On my laptop, it takes just a little over two seconds to run with the pre-aggregation. It takes more than 8 seconds without it.
For learning much more about the ancient "Black Arts" CrossTab method, pre-aggregation, a comparison against PIVOT (which sucks), and how to do the dynamic SQL without the use of STRING_AGG() or XML PATH concatenation, please see the following two almost-as-ancient articles on how to "convert rows to columns".
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
For more information on how to quickly and easily make shedloads of test data in multiple different forms for many scenarios, please see the following articles...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Very cool, just what I was looking for...
Many Thanks for all replies...
-
I was going to ask is there any easy way to port the output to an Xcel file?
Thanks.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply