pivot on 2 fields

Question

Post reply

pivot on 2 fields

Bruin

SSCrazy

Points: 2058

March 1, 2021 at 1:04 am

#3851448

I'm trying to create a query that pivots on 2 fields(entity,channel) and produce a daily sum of charges per day.

This gives me a daily total( I would like to remove any Null values from the display) for the month, but I want to break it

down by channel within the entity,

Data:

src,200,02,01,400.00

xrb,200,02,01,500.00

src,300,02,01,400.00

xrb,300,02,01,500.00

desired output

entity

01 02

200

src 400 0

xrb 500 0

300

src 0 400

xrb 0 500

Thanks.

CREATE TABLE #MonthlyChrgs (
tmp_channel nvarchar(10),
tmp_entity nvarchar(10),
tmp_month nvarchar(10),
tmp_day nvarchar(10),
tmp_charges dec(18,2)



DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(tmp_day) 
                    from #MonthlyChrgs
                    group by tmp_day
                    order by tmp_day
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT tmp_entity,' + @cols + ' from 
             (
                select tmp_entity,tmp_channel, tmp_day, tmp_Charges
                from #MonthlyChrgs
            ) x
            pivot 
            (
                sum( tmp_Charges)
                for tmp_day in (' + @cols + ')
            ) p '

execute(@query);

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

An alternative to PIVOT is conditional aggregation. The sample data provided only contains 1 value for 'tmp_day' which seems to not agree with the output. So I changed the sample data so that 'tmp_day' contains both '01' and '02' to match with the output. Also, it's not clear if it's necessary for this to be dynamic SQL. The column 'tmp_day' is nvarchar(10) which might suggest there are a fixed number of values. Maybe you're looking for something like this

drop table if exists #MonthlyChrgs;
go
CREATE TABLE #MonthlyChrgs (
tmp_channel nvarchar(10),
tmp_entity nvarchar(10),
tmp_month nvarchar(10),
tmp_day nvarchar(10),
tmp_charges dec(18,2));

insert into #MonthlyChrgs(tmp_channel, tmp_entity, tmp_month, tmp_day, tmp_charges) values
('src','200','02','01',400.00),
('xrb','200','02','01',500.00),
('src','300','02','02',400.00),
('xrb','300','02','02',500.00);

select tmp_channel, tmp_entity, 
       sum(case when tmp_day='01' then tmp_charges else 0 end) day1,
       sum(case when tmp_day='02' then tmp_charges else 0 end) day2,
       sum(case when tmp_day='03' then tmp_charges else 0 end) day3,
       sum(case when tmp_day='04' then tmp_charges else 0 end) day4,
       sum(case when tmp_day='05' then tmp_charges else 0 end) day5,
       sum(case when tmp_day='06' then tmp_charges else 0 end) day6,
       sum(case when tmp_day='07' then tmp_charges else 0 end) day7
from #MonthlyChrgs
group by tmp_channel, tmp_entity
order by tmp_entity, tmp_channel;

tmp_channeltmp_entityday1day2day3day4day5day6day7
src200400.000.000.000.000.000.000.00
xrb200500.000.000.000.000.000.000.00
src3000.00400.000.000.000.000.000.00
xrb3000.00500.000.000.000.000.000.00

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Bruin SSCrazy Points: 2058 More actions · Answer 2

I was trying to get the days so they would be dynamic rather than having to hard those each month.. I also wanted

the channel to be sub-category of the entity..

Thanks.

Steve Collins SSCrazy Points: 2157 More actions · Answer 3

Based on the data that's been provided the two tmp_day values are '01' and '02' which don't seem to vary by month. As to which column is subordinate to the other, it could be switched in the GROUP BY/ORDER BY clause(s) to whatever is appropriate

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Bruin wrote:

I was trying to get the days so they would be dynamic rather than having to hard those each month.. I also wanted the channel to be sub-category of the entity..
Thanks.

The original name for such "conditional aggregations" is CROSSTABs. To do them fairly easily using CROSSTABs, please see the following article.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

They were a life saver for me in the past.

There's also a first article on the subject which compares the performance (on older machines) of CROSSTABs vs PIVOTS. In most cases, CROSSTABs (especially with "pre-aggregation" if it's needed) pan out to be quite a bit faster than PIVOTs.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%e2%80%93-converting-rows-to-columns-1

--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)

Bruin SSCrazy Points: 2058 More actions · Answer 5

I would like to be able to run this for a given Year, is that possible using crosstabs?

Thanks again

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

Bruin wrote:

I would like to be able to run this for a given Year, is that possible using crosstabs?
Thanks again

How are you going to present a row with 365/366 columns to a client? And how many years would be included - 3 years, 10 years - 100 years?

I would rollup the numbers to either a weekly (52/53 weeks) or monthly (12 month) cross-tab (pivot). For reporting you can then drill-down or drill-through to expand for a given time frame. For example, user selects the totals column for month 10 last year and opens a report that displays that year and months detail data.

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

Bruin SSCrazy Points: 2058 More actions · Answer 7

I want to show current year and previous.. do you have any examples based upon my current query?

Thanks again...

This reply was modified 3 years, 8 months ago by Bruin.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Bruin wrote:

I want to show current year and previous.. do you have any examples based upon my current query?
Thanks again...

I'm pretty confused about what you're actually using for a source table and what you want the output to look like. Take a look at the first link in my signature line below for one way to post sample data, please. A cleaner description of what you want for the output would be helpful, as well.

And none of this is difficult... we just need to know the right "gazintas" and "gozotas". 😀

--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)

Bruin SSCrazy Points: 2058 More actions · Answer 9

Data:

src,200,2021,01,01,400.00

xrb,200,2021,01,01,500.00

src,300,2021,02,01,400.00

xrb,300,2021,02,01,500.00

I would like to rollup the data at a monthly level by year entity and channel. If no data reported for the month than 0 displayed

Jan Feb Mar May ...

Year 2021

entity 200

channel src 400 0

xrb 500 0

entity 300

src 0 400

xrb 0 500

I'm trying to get a breakdown by Year,Entity and channel for charges.

Thanks.

CREATE TABLE #MonthlyChrgs (
tmp_channel nvarchar(10),
tmp_entity nvarchar(10),
        tmp_year nvarchar(4),
tmp_month nvarchar(10),
tmp_day nvarchar(10),
tmp_charges dec(18,2)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

It would have been much more neighborly of you if you had read the article at the link I directed you to and provided the data in a "readily consumable" format. If you want help in the future, consider doing such a thing on future posts. 😀

Here's the code with the data you provided in a readily consumable format (one of many methods).

   DROP TABLE IF EXISTS #MonthlyChrgs;
 CREATE TABLE #MonthlyChrgs 
        (
         tmp_channel    NVARCHAR(10)
        ,tmp_entity     NVARCHAR(10)
        ,tmp_year       NVARCHAR(4)
        ,tmp_month      NVARCHAR(10)
        ,tmp_day        NVARCHAR(10)
        ,tmp_charges    DEC(18,2)
        )
;
 INSERT INTO #MonthlyChrgs
        (tmp_channel, tmp_entity, tmp_year, tmp_month, tmp_day, tmp_charges)
 VALUES --Data for 2021
         ('src','200','2021','01','01',400.00)
        ,('xrb','200','2021','01','01',500.00)
        ,('src','300','2021','02','01',400.00)
        ,('xrb','300','2021','02','01',500.00)
        --Data for 2020
        ,('src','200','2020','01','01',400.00)
        ,('xrb','200','2020','01','01',500.00)
        ,('src','300','2020','02','01',400.00)
        ,('xrb','300','2020','02','01',500.00)
;

As a bit of a sidebar, consider NOT using NVARCHAR() for numeric data.

Here's the "simple" code to do only what you ask. If you need subtotals, line totals, etc, etc, let us know.

DECLARE @Year INT = 2021
;
--===== Solve the problem using a CROSSTAB, like what is in the article links I posted.
 SELECT  tmp_year
        ,tmp_channel
        ,tmp_entity
        ,[Jan] = SUM(CASE WHEN tmp_month = '01' THEN tmp_charges ELSE 0 END)
        ,[Feb] = SUM(CASE WHEN tmp_month = '02' THEN tmp_charges ELSE 0 END)
        ,[Mar] = SUM(CASE WHEN tmp_month = '03' THEN tmp_charges ELSE 0 END)
        ,[Apr] = SUM(CASE WHEN tmp_month = '04' THEN tmp_charges ELSE 0 END)
        ,[May] = SUM(CASE WHEN tmp_month = '05' THEN tmp_charges ELSE 0 END)
        ,[Jun] = SUM(CASE WHEN tmp_month = '06' THEN tmp_charges ELSE 0 END)
        ,[Jul] = SUM(CASE WHEN tmp_month = '07' THEN tmp_charges ELSE 0 END)
        ,[Aug] = SUM(CASE WHEN tmp_month = '08' THEN tmp_charges ELSE 0 END)
        ,[Sep] = SUM(CASE WHEN tmp_month = '09' THEN tmp_charges ELSE 0 END)
        ,[Oct] = SUM(CASE WHEN tmp_month = '10' THEN tmp_charges ELSE 0 END)
        ,[Nov] = SUM(CASE WHEN tmp_month = '11' THEN tmp_charges ELSE 0 END)
        ,[Dec] = SUM(CASE WHEN tmp_month = '12' THEN tmp_charges ELSE 0 END)
        ,[LineTotal] = SUM(tmp_charges)
   FROM #MonthlyChrgs
  WHERE tmp_year IN (@Year, @Year-1)
  GROUP BY tmp_year, tmp_channel, tmp_entity
  ORDER BY tmp_year, tmp_channel, tmp_entity
;

Here are the results...

--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)

Bruin SSCrazy Points: 2058 More actions · Answer 11

Bruin

SSCrazy

Points: 2058

March 3, 2021 at 3:38 pm

#3852930

Cool Thanks again!!!!