Case Dups - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Case Dups

Author

Topic

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-06-26 : 06:22:15

I am writing a case statement (see below) but want to get a unique value where I have it. How can i get it to only give me 1 line per URN and the values as seperate products?

Raw Data:

URN value Product_code
123 1 A
123 2 A
123 3 B
123 4 C

SELECT DISTINCT URN,
CASE WHEN PRODUCT_CODE IN ('A') THEN SUM(ISNULL([VALUE],0))
END AS PROD_A,
CASE WHEN PRODUCT_CODE IN ('B') THEN SUM(ISNULL([VALUE],0))
END AS PROD_B,
CASE WHEN PRODUCT_CODE IN ('C') THEN SUM(ISNULL([VALUE],0))
END AS PROD_C
FROM EXAMPLE
GROUP BY URN, VALUE

The data returned is:

URN PROD_A PROD_B PROD_C
123 NULL NULL 4
123 NULL 3 NULL
123 3 NULL NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 06:25:44

[code]SELECT URN,
SUM(CASE WHEN Product_code='A' THEN Value ELSE 0 END) AS PROD_A,
SUM(CASE WHEN Product_code='B' THEN Value ELSE 0 END) AS PROD_B,
SUM(CASE WHEN Product_code='C' THEN Value ELSE 0 END) AS PROD_C
FROM EXAMPLE
GROUP BY URN[/code]

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-06-26 : 06:33:35

That is still returning the data the same way?

URN PROD_A PROD_B PROD_C
123 NULL NULL 4
123 NULL 3 NULL
123 3 NULL NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 06:57:32

quote:
Originally posted by starnold

That is still returning the data the same way?

URN PROD_A PROD_B PROD_C
123 NULL NULL 4
123 NULL 3 NULL
123 3 NULL NULL

it wont if you GROUP only by URN.(see my posted query)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 07:03:12

[code]declare @test table
(
URN int, value int, Product_code char(1)
)
INSERT INTO @test
SELECT 123, 1, 'A' UNION ALL
SELECT 123, 2, 'A' UNION ALL
SELECT 123, 3, 'B' UNION ALL
SELECT 123, 4, 'C' UNION ALL
SELECT 124, 13, 'A' UNION ALL
SELECT 124, 22, 'A' UNION ALL
SELECT 124, 30, 'C' UNION ALL
SELECT 124, 14, 'C' UNION ALL
SELECT 125, 11, 'A' UNION ALL
SELECT 125, 32, 'A' UNION ALL
SELECT 125, 13, 'B' UNION ALL
SELECT 123, 40, 'C'

SELECT URN,
SUM(CASE WHEN Product_code='A' THEN Value ELSE 0 END) AS PROD_A,
SUM(CASE WHEN Product_code='B' THEN Value ELSE 0 END) AS PROD_B,
SUM(CASE WHEN Product_code='C' THEN Value ELSE 0 END) AS PROD_C
FROM @test
GROUP BY URN

output
-----------------------
URN PROD_A PROD_B PROD_C
----------- ----------- ----------- -----------
123 3 3 44
124 35 0 44
125 43 13 0[/code]

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-06-26 : 08:21:42

oops sorry missed that part, thanks again working now =)

YOU ARE A STAR!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 10:49:31