March 23, 2012 at 10:46 am
I hope this is the correct forum. I have a simple table with a few columns. In a view, I want to sum and group various rows into a new columns based on values form the category column.
Example Table:
SourceCategory Currency
Source 1CAA 18.00
Source 1DAA 15.00
Source 1EAA 12.00
Source 1PRI 10.00
Source 1 BRI 15.00
Source 1AAS 12.00
Source 1BSS 10.00
Source 1CSS 5.00
In my View I want to take all the CAA, DAA, EAA and PRI currency values and Sum them and put them in a column called Type 1
I want to take all the BRI, AAS, BSS and CSS currency values and put them in a new column called Type 2.
The result being:
Example View:
SourceType 1 (CAA,DAA,EAA,PRI Categories)Type 2 (BRI,AAS,BSS,CSS Categories)
Source 155.00 (this is the sum) 42.00 (this is the sum)
I hope this makes sense. Can anyone help me out? I'm trying different methods of grouping and sums, but I can't get these value totals into new columns.
Thanks
March 23, 2012 at 11:23 am
This?
DECLARE @Tab TABLE
(
Src VARCHAR(10)
,Category VARCHAR(3)
,Amount DECIMAL(10,2)
)
INSERT INTO @Tab
SELECT 'Source 1' , 'CAA' ,18.00
UNION ALL SELECT 'Source 1' , 'DAA' ,15.00
UNION ALL SELECT 'Source 1' , 'EAA' ,12.00
UNION ALL SELECT 'Source 1' , 'PRI' ,10.00
UNION ALL SELECT 'Source 1' , 'BRI' ,15.00
UNION ALL SELECT 'Source 1' , 'AAS' ,12.00
UNION ALL SELECT 'Source 1' , 'BSS' ,10.00
UNION ALL SELECT 'Source 1' , 'CSS' ,5.00
; WITH CTE AS
(
SELECT T.Src
, 'CAA' = MAX ( CASE WHEN T.Category = 'CAA' THEN T.Amount ELSE 0 END )
, 'DAA' = MAX ( CASE WHEN T.Category = 'DAA' THEN T.Amount ELSE 0 END )
, 'EAA' = MAX ( CASE WHEN T.Category = 'EAA' THEN T.Amount ELSE 0 END )
, 'PRI' = MAX ( CASE WHEN T.Category = 'PRI' THEN T.Amount ELSE 0 END )
, 'BRI' = MAX ( CASE WHEN T.Category = 'BRI' THEN T.Amount ELSE 0 END )
, 'AAS' = MAX ( CASE WHEN T.Category = 'AAS' THEN T.Amount ELSE 0 END )
, 'BSS' = MAX ( CASE WHEN T.Category = 'BSS' THEN T.Amount ELSE 0 END )
, 'CSS' = MAX ( CASE WHEN T.Category = 'CSS' THEN T.Amount ELSE 0 END )
FROM @Tab T
GROUP BY T.Src
)
SELECT C.Src
,Type1 = C.CAA + C.DAA + C.EAA + C.PRI
,Type2 = C.AAS +C.BRI + C.BSS + C.CSS
FROM CTE C
March 23, 2012 at 11:44 am
I will try that, thank you. I think Union is what I was looking for.
March 23, 2012 at 11:49 am
defiantclass (3/23/2012)
I will try that, thank you. I think Union is what I was looking for.
Union ? Why union? I gave a cross-tab type query. The UNION ALL is for populating the sample data..
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply