Database
Enthusiast

## Pivot by Cumulative Sum

I need to pivot a table based on month and cumulative sum of the quantity. I have given the below table and expected result.  There may be few Months missing for a given date and state_Cd in the data. While pivoting, I should take a cumulative sum of the quantity across months. Can you please help me with the Teradata SQL query? I am using TD 14.

 Date State_CD Month Quantity 1/11/16 1234 0 10 1/11/16 1234 1 20 1/11/16 1234 2 30 1/11/16 1234 3 10 1/11/16 1234 4 50 1/11/16 1234 5 20 11/26/18 5556 1 10 11/26/18 5556 3 20

Expected Result:

 date state_cd month 0 month 1 month 2 month 3 month 4 month 5 1/11/16 1234 10 30 60 70 120 140 11/26/18 5556 0 10 10 30 30 30

Accepted Solutions
Highlighted
Junior Contributor

## Re: Pivot by Cumulative Sum

Hi @Waldar, maybe I didn't understand the question correctly, but a simple Pivot query returns the same result without any additional step:

```SELECT TheDate, State_Cd
,Sum(CASE WHEN Month_Id BETWEEN 0 AND 0 THEN Quantity ELSE 0 end) AS Month_0
,Sum(CASE WHEN Month_Id BETWEEN 0 AND 1 THEN Quantity ELSE 0 end) AS Month_1
,Sum(CASE WHEN Month_Id BETWEEN 0 AND 2 THEN Quantity ELSE 0 end) AS Month_2
,Sum(CASE WHEN Month_Id BETWEEN 0 AND 3 THEN Quantity ELSE 0 end) AS Month_3
,Sum(CASE WHEN Month_Id BETWEEN 0 AND 4 THEN Quantity ELSE 0 end) AS Month_4
,Sum(CASE WHEN Month_Id BETWEEN 0 AND 5 THEN Quantity ELSE 0 end) AS Month_5
FROM mvt_datas
GROUP BY TheDate, State_Cd```

1 ACCEPTED SOLUTION
4 REPLIES

## Re: Pivot by Cumulative Sum

Hi vinosql,

I've done your query using several techniques.

The first one is to create a complete dataset of Date, State and Month.

The second one is to compute the cumulative sum.

The last one is the pivot.

Data

```create multiset volatile table mvt_datas, no log
( TheDate       date format 'yyyy-mm-dd'
, State_Cd      integer
, Month_Id      byteint
, Quantity      smallint
)
primary index (TheDate, State_Cd)
on commit preserve rows;

insert into mvt_datas values (date '2016-01-11', 1234, 0, 10);
insert into mvt_datas values (date '2016-01-11', 1234, 1, 20);
insert into mvt_datas values (date '2016-01-11', 1234, 2, 30);
insert into mvt_datas values (date '2016-01-11', 1234, 3, 10);
insert into mvt_datas values (date '2016-01-11', 1234, 4, 50);
insert into mvt_datas values (date '2016-01-11', 1234, 5, 20);
insert into mvt_datas values (date '2018-11-26', 5556, 1, 10);
insert into mvt_datas values (date '2018-11-26', 5556, 3, 20);```

Query

```with cte_cumumative_sum (TheDate, State_Cd, Month_Id, Quantity) as
(
select cte.TheDate, cte.State_Cd, cte.Month_Id
, sum(coalesce(mvt.Quantity, 0)) over(partition by cte.TheDate, cte.State_Cd
order by cte.Month_Id asc rows between unbounded preceding and current row)
from cte_expand as cte
left join mvt_datas  as mvt  on mvt.TheDate  = cte.TheDate
and mvt.State_Cd = cte.State_Cd
and mvt.Month_Id = cte.Month_Id
)
,  cte_expand (TheDate, State_Cd, Month_Id) as
(
select TheDate
, State_Cd
, begin(prd) - current_date
from cte_distinct_axes
expand on period(current_date, current_date + 6) as prd by interval '1' day
)
,  cte_distinct_axes (TheDate, State_Cd) as
(
select TheDate, State_Cd
from mvt_datas
group by TheDate, State_Cd
)
select TheDate, State_Cd
, max(case Month_Id when 0 then Quantity else 0 end) as Month_0
, max(case Month_Id when 1 then Quantity else 0 end) as Month_1
, max(case Month_Id when 2 then Quantity else 0 end) as Month_2
, max(case Month_Id when 3 then Quantity else 0 end) as Month_3
, max(case Month_Id when 4 then Quantity else 0 end) as Month_4
, max(case Month_Id when 5 then Quantity else 0 end) as Month_5
from cte_cumumative_sum
group by TheDate, State_Cd;```

Enthusiast

## Re: Pivot by Cumulative Sum

I do not the get the expected result from the query and I do not want to expand the date field as well.

Highlighted
Junior Contributor

## Re: Pivot by Cumulative Sum

Hi @Waldar, maybe I didn't understand the question correctly, but a simple Pivot query returns the same result without any additional step:

```SELECT TheDate, State_Cd
,Sum(CASE WHEN Month_Id BETWEEN 0 AND 0 THEN Quantity ELSE 0 end) AS Month_0
,Sum(CASE WHEN Month_Id BETWEEN 0 AND 1 THEN Quantity ELSE 0 end) AS Month_1
,Sum(CASE WHEN Month_Id BETWEEN 0 AND 2 THEN Quantity ELSE 0 end) AS Month_2
,Sum(CASE WHEN Month_Id BETWEEN 0 AND 3 THEN Quantity ELSE 0 end) AS Month_3
,Sum(CASE WHEN Month_Id BETWEEN 0 AND 4 THEN Quantity ELSE 0 end) AS Month_4
,Sum(CASE WHEN Month_Id BETWEEN 0 AND 5 THEN Quantity ELSE 0 end) AS Month_5
FROM mvt_datas
GROUP BY TheDate, State_Cd```