by ssi
1. May 2013 09:06
Tale [material] Table and conver to columns
SELECT [BATCH_NO]
,[BATCH_DAT]
,[MATERIAL]
,[NET_WT]
,[ID]
FROM [rcc_production].[dbo].[material]
SELECT BATCH_NO, SUM(CASE [MATERIAL] WHEN 1 THEN [NET_WT] ELSE 0 END) AS Bin1, SUM(CASE [MATERIAL] WHEN 2 THEN [NET_WT] ELSE 0 END) AS Bin2,
SUM(CASE [MATERIAL] WHEN 3 THEN [NET_WT] ELSE 0 END) AS Bin3, SUM(CASE [MATERIAL] WHEN 4 THEN [NET_WT] ELSE 0 END) AS Bin4,
SUM(CASE [MATERIAL] WHEN 5 THEN [NET_WT] ELSE 0 END) AS Bin5, SUM(CASE [MATERIAL] WHEN 6 THEN [NET_WT] ELSE 0 END) AS Bin6,
SUM(CASE [MATERIAL] WHEN 7 THEN [NET_WT] ELSE 0 END) AS Bin7, SUM(CASE [MATERIAL] WHEN 8 THEN [NET_WT] ELSE 0 END) AS Bin8,
SUM(CASE [MATERIAL] WHEN 9 THEN [NET_WT] ELSE 0 END) AS Bin9, SUM(CASE [MATERIAL] WHEN 10 THEN [NET_WT] ELSE 0 END) AS Bin10,
SUM(CASE [MATERIAL] WHEN 11 THEN [NET_WT] ELSE 0 END) AS Bin11, SUM(CASE [MATERIAL] WHEN 12 THEN [NET_WT] ELSE 0 END) AS Bin12,
SUM(CASE WHEN [MATERIAL] > 12 THEN [NET_WT] ELSE 0 END) AS HAND_LBS, SUM(CASE WHEN [MATERIAL] > 12 THEN 1 ELSE 0 END) AS HAND_ADDS
FROM dbo.material
GROUP BY BATCH_NO
9aa4d32f-fdf8-44ad-a558-a63d4098ac46|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags: CASE, SUM
SQL