April 9, 2010 at 1:47 am
Hi All
I have a single table with 2 analys codes what I need to do is this
For each cost code I have 2 cost types ie cost code ENERGY1 in AN_CODE02 has 2 rows AN_CODE01 CAP & CUR
What I need to do is create a view that gives me one row for each AN_CODE02 with the AN_CODE01 as columbs with their repective SUM from the grouping
IE current data grouped
Col AN_CODE01 ¦ AN_CODE02 ¦ VALUE
Row CAP ENERGY1 200
CUR ENERGY1 400
The result I need is this
Col AN_CODE02 ¦ CAP ¦ CUR ¦ TOTAL
ENERGY1 200 400 600
Thanks for the help in advance
PS I know i could do this with multipul views but I want to know if I can do this as one view please?
April 9, 2010 at 2:03 am
This should help you out..
DECLARE @tblTable TABLE
(
AN_CODE1 VARCHAR(50),
AN_CODE2 VARCHAR(50),
[VALUE] INT
)
INSERT @tblTable
SELECT 'CAP', 'ENERGY1', 200 UNION ALL
SELECT 'CUR', 'ENERGY1', 400
SELECTAN_CODE2,
SUM( CASE WHEN AN_CODE1 = 'CAP' THEN [VALUE] ELSE 0 END ) CAP,
SUM( CASE WHEN AN_CODE1 = 'CUR' THEN [VALUE] ELSE 0 END ) CUR,
SUM( [VALUE] ) Total
FROM@tblTable
GROUP BY AN_CODE2
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 9, 2010 at 2:40 am
Thanks Kingston
I used this code sction on the actual table
Have a nice weekend
SELECT AN_CODE2,
SUM( CASE WHEN AN_CODE1 = 'CAP' THEN [VALUE] ELSE 0 END ) CAP,
SUM( CASE WHEN AN_CODE1 = 'CUR' THEN [VALUE] ELSE 0 END ) CUR,
SUM( [VALUE] ) Total
FROM @tblTable
GROUP BY AN_CODE2
April 9, 2010 at 3:18 am
Glad to help you out:-)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply