CODEWITHSUNDEEP
sql-serverpivot
blankon91
blankon91

Reputation: 515

How to pivot rows by grouping the rows?

I have query to show the table like this:
enter image description here

but I want to PIVOT it by grouping bulan and the table result like this:
enter image description here

and this is query that I use to get my current result:

SELECT 
    month([date]) as bulan, 
    [type] as tipe,
    SUM([net qty]) total_karton, 
    CAST(SUM([cm1 (rp)]) as decimal) as total_uang
FROM 
    tbl_weeklyflash_ID
WHERE
    DATEDIFF(month,[date],CURRENT_TIMESTAMP) between 0 and 2
GROUP BY 
    month([date]),
    [type]
ORDER BY 
    month([date]), [type]

Upvotes: 0

Views: 249

Answers (2)

Joe G Joseph
Joe G Joseph

Reputation: 24046

If the number of rows per group is dynamic and but the number of groups is 3 always, then try this code

;with maintable as (select month([date]) m_date,row_number() over(order by count(*) desc) row_num from tbl_weeklyflash_ID WHERE DATEDIFF(month,[date],CURRENT_TIMESTAMP) between 0 and 2
group by month([date])),
t1 as ( select month([date]) as bulan, [type] as tipe,SUM([net qty]) total_karton, CAST(SUM([cm1 (rp)]) as decimal) as total_uang,row_number() over(order by month([date])) as slno  from tbl_weeklyflash_ID where month([date]) =(select m_date from maintable where row_num=1) GROUP BY month([date]), [type]) ,
t2 as ( select month([date]) as bulan, [type] as tipe,SUM([net qty]) total_karton, CAST(SUM([cm1 (rp)]) as decimal) as total_uang,row_number() over(order by month([date])) as slno  from tbl_weeklyflash_ID where month([date]) =(select m_date from maintable where row_num=2) GROUP BY month([date]), [type]),
t3 as ( select month([date]) as bulan, [type] as tipe,SUM([net qty]) total_karton, CAST(SUM([cm1 (rp)]) as decimal) as total_uang,row_number() over(order by month([date])) as slno  from tbl_weeklyflash_ID where month([date]) =(select m_date from maintable where row_num=3) GROUP BY month([date]), [type])
select t1.tipe,t1.total_karton [karton1],t1.total_uang [uang1],t2.total_karton [karton2],t2.total_uang [uang2],t3.total_karton [karton3],t3.total_uang [uang3]
from t1 full outer join t2
on t1.slno=t2.slno 
full outer join t3
on t2.slno =t3.slno

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

This is not the exact solution. If you have the number of rows per group is constant always as shown in the example (5 for this example) , you can do something like this..

declare @row_count int =6;
    with cte as(
        select  month([date]) as bulan, 
        [type] as tipe,
        SUM([net qty]) total_karton, 
        CAST(SUM([cm1 (rp)]) as decimal) as total_uang,row_number() over(order 
    by month([date]),[type]) as rnk 
        from tbl_weeklyflash_ID 
        WHERE
        DATEDIFF(month,[date],CURRENT_TIMESTAMP) between 0 and 2
        GROUP BY 
        month([date]),
        [type]
        )
    select * from cte T1 join cte T2 
    on T1.rnk= T2.rnk-@row_count 
    and t1.rnk between 1 and @row_count
    join cte T3
    on  T2.rnk= T3.rnk-@row_count 
    and t2.rnk between 1*@row_count+1 and 2*@row_count

here you have to add as many join conditions as the number of groups..

Upvotes: 1

Related Questions