conditional query - 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

General SQL Server Forums

New to SQL Server Programming

conditional query

Author

Topic

jthomas
Starting Member

3 Posts

Posted - 2008-09-10 : 23:51:06

I have a table Details with columns
Store_Nbr,Week and Prodt_Nbr as the composite primary key.
Sales is another column in the same table

Data in my table is like as given below
TABLE DATA
Week Prodt_Nbr Store_Nbr Sales
200810 Prod06 101 4860
200810 Prod08 101 2526
200810 Prod06 103 2259
200810 Prod08 103 3222
200810 Prod06 109 3452
200811 Prod08 109 7322

For each store number, i want to select the sales column values fetched as different column names(eg:APPLIANCES_SALES or ELECTRONICS_SALES )
based on values which come in Prodt_Nbr column.
That is if the Prodt_Nbr column value is Prod06 then the query should fetch 4860 as APPLIANCES_SALES and 0 for ELECTRONICS_SALES

desired output

101 4860 2526
103 2259 3222

Please send me the query for fetching the result as explained

Thank in advance
Thomas

hey001us
Posting Yak Master

185 Posts

Posted - 2008-09-10 : 23:59:24

CASE WHEN Prodt_Nbr = Prod06 THEN APPLIANCES_SALES ELSE ELECTRONICS_SALES
END SALES

hey

jthomas
Starting Member

3 Posts

Posted - 2008-09-11 : 00:09:01

I have to group the results on Store_Nbr.How wou ld i go about grouping on only one of the key columns alone ?
Could you help with this query to achieve this ?

jthomas
Starting Member

3 Posts

Posted - 2008-09-11 : 01:36:58

I tried a sql like

SELECT Store_Nbr,
CASE WHEN MM.Prodt_Nbr = 'Prod06' THEN Max(MM.Sales) ELSE '0' END AS APPLIANCES_SALES,
CASE WHEN MM.Prodt_Nbr = 'Prod08' THEN Max(MM.Sales) ELSE '0' END AS ELECTRONICS_SALES
CASE WHEN MM.Prodt_Nbr = 'Prod01' THEN Max(MM.Sales) ELSE '0' END AS CAM_SALES
FROM Details MM(NOLOCK)
WHERE Week = 200828
GROUP BY
Store_Nbr

But i does not work. How to modify this?

hey001us
Posting Yak Master

185 Posts

Posted - 2008-09-11 : 12:32:07

SELECT Store_Nbr,
MAX(CASE WHEN MM.Prodt_Nbr = 'Prod06' THEN MM.Sales ELSE '0' END) AS APPLIANCES_SALES,
MAX(CASE WHEN MM.Prodt_Nbr = 'Prod08' THEN MM.Sales ELSE '0' END) AS ELECTRONICS_SALES,
MAX(CASE WHEN MM.Prodt_Nbr = 'Prod01' THEN MM.Sales ELSE '0' END) AS CAM_SALES
FROM (
SELECT 200810 [Week], 'Prod06' Prodt_Nbr, 101 Store_Nbr, 4860 Sales
UNION ALL SELECT 200810, 'Prod08', 101, 2526
UNION ALL SELECT 200810, 'Prod06', 103, 2259
UNION ALL SELECT 200810, 'Prod08', 103, 3222
UNION ALL SELECT 200810, 'Prod06', 109, 3452
UNION ALL SELECT 200811, 'Prod08', 109, 7322
) MM
WHERE Week = 200810
GROUP BY
Store_Nbr

OUTPUT:
Store_Nbr APPLIANCES_SALES ELECTRONICS_SALES CAM_SALES
----------- ---------------- ----------------- -----------
101 4860 2526 0
103 2259 3222 0
109 3452 0 0

(3 row(s) affected)

hey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources