Data roll up

Question

Post reply

Data roll up

sqlenthu 89358

Hall of Fame

Points: 3069
More actions
February 2, 2018 at 6:21 am

#410192

Hi all,
I have one table which contains data like this:
Col1,Col2,Col3
A,before,100
A,after,10
A,after,80
B,before,280
B,before,20
B,after,250
C,before,150
C,after,220
Now i want my output as
Col1,Col2,Amt
A,before,100
A,after,90
A,Diff,10
B,before,300
B,after,250
B,Diff,50
C,before,150
C,after,220
C,Diff,-70
The before, after have to be rolled up and diff has to be calculated based on the respective rolled up before and after amount. Any efficient way ?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

You know by now how to post a T-SQL question. Please supply DDL and Consumable Sample Data.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Bert-701015 SSCommitted Points: 1615 More actions · Answer 2

Try this out:

DECLARE @Table table (
    Col1 char(1),
    Col2 varchar(6),
    Col3 smallint
)
;
insert @Table
    values
        ('A','before',100),
        ('A','after',10),
        ('A','after',80),
        ('B','before',280),
        ('B','before',20),
        ('B','after',250),
        ('C','before',150),
        ('C','after',220)
;
With groups (Col1, Col2, Col3) as (
    select Col1, Col2, sum(Col3) AS Col3
    from @Table
    group by Col1, Col2
)
select Col1, Col2, Amt
from (
    select '1' as Seq, Col1, Col2, Col3 as Amt from Groups where Col2 = 'before'
    union all
    select '2' as Seq, Col1, Col2, Col3 as Amt from Groups where Col2 = 'after'
    union all
    select '3' as Seq, B.Col1, 'Diff' as Col2, B.Col3 - A.Col3 as Amt
    from Groups B
    inner join Groups A on B.Col1 = A.Col1
    where B.Col2 = 'before'
    and A.Col2 = 'after'
) r
order by Col1, Seq

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

Using the sample data DDL Bert generously supplied, here's an alternative that reads the base table 1 time instead of 6:

SELECT Col1, Col2=CASE WHEN GROUPING(Col2)=1 THEN 'Diff' ELSE Col2 END, Col3=CASE WHEN GROUPING(Col2)=1 THEN SUM(CASE WHEN Col2='before' THEN Col3 ELSE 0 END)-SUM(CASE WHEN Col2='after' THEN Col3 ELSE 0 END) ELSE SUM(Col3) END FROM @Table GROUP BY GROUPING SETS ((Col1,Col2),(Col1)) ORDER BY Col1,CASE WHEN Col2='before' THEN 0 WHEN Col2='After' THEN 1 ELSE 2 END;

Cheers!

drew.allen SSC Guru Points: 76944 More actions · Answer 4

This gives the expected results and only requires one scan of the table.

SELECT col1, CASE WHEN GROUPING(col2) = 1 THEN 'diff' ELSE col2 END AS col2, CASE WHEN col2 = 'after' THEN -1 ELSE 1 END * SUM(CASE WHEN col2 = 'before' THEN col3 ELSE -col3 END) FROM @Table GROUP BY GROUPING SETS( (col1, col2), (col1) ) ORDER BY col1, GROUPING(col2), col2 DESC

Drew

Edit: Removed extraneous GROUPING(col1) from the ORDER BY clause.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA