February 20, 2007 at 12:30 am
Hello can someone please help with this problem - I have a table
sampleno workcode result
211 33 50
211 33.01 51
211 33.02 54
211 33.03 32
212 33 23
212 33.01 34
212 33.02 24
212 33.03 11
213 33.01 35
214 33 36
214 33.01 34
214 33.02 45
I would like to create a View returning the following output
SampleNo 33_00 33_01 33_02 33_03
211 50 51 54 32
212 23 34 24 11
213 35
214 36 34 45
I have tried the following Case statement
SELECT [Sample Number],
CASE WHEN 33 = [WORKCODE] THEN [RESULT] END AS [33_00],
CASE WHEN 33.01 = [WORKCODE] THEN [RESULT] END AS [33_01],
CASE WHEN 33.02 = [WORKCODE] THEN [RESULT] END AS [33_02],
CASE WHEN 33.03 = [WORKCODE] THEN [RESULT] END AS [33_03],
FROM dbo.[Other Sample Results]
GROUP BY [Sample Number],CASE WHEN 33 = [WORKCODE] THEN [RESULT] END AS [33_00], CASE WHEN
33.01 = [WORKCODE] THEN [RESULT] END, CASE WHEN 33.02 = [WORKCODE] THEN [RESULT] END,
CASE WHEN 33.03 = [WORKCODE] THEN [RESULT] END, CASE WHEN 33.04 =
[WORKCODE] THEN [RESULT] END,
The output I get is not grouping the records into a single record entry.
SampleNo 33_00 33_01 33_02 33_03
211 50
211 51
211 54
211 32
212 23
212 34
212 24
Can someone please help out with a query to get the records grouping in a single entry as SampleNo 33_00 33_01 33_02 33_03
211 50 51 54 32
212 23 34 24 11
213 35
214 36 34 45
Thank you
February 20, 2007 at 2:57 am
You need to use an aggregate function. SUM will be the most efficient although AVG would also work.
-- *** Test Data ***
DECLARE @t TABLE
(
sampleno int NOT NULL
,workcode decimal(18,2) NOT NULL
,result int NOT NULL
)
INSERT INTO @t
SELECT 211, 33, 50 UNION ALL
SELECT 211, 33.01, 51 UNION ALL
SELECT 211, 33.02, 54 UNION ALL
SELECT 211, 33.03, 32 UNION ALL
SELECT 212, 33, 23 UNION ALL
SELECT 212, 33.01, 34 UNION ALL
SELECT 212, 33.02, 24 UNION ALL
SELECT 212, 33.03, 11 UNION ALL
SELECT 213, 33.01, 35 UNION ALL
SELECT 214, 33, 36 UNION ALL
SELECT 214, 33.01, 34 UNION ALL
SELECT 214, 33.02, 45
-- *** End Test Data ***
SELECT
sampleno AS SampleNo
,SUM(CASE workcode WHEN 33 THEN result END) AS [33_00]
,SUM(CASE workcode WHEN 33.01 THEN result END) AS [33_01]
,SUM(CASE workcode WHEN 33.02 THEN result END) AS [33_02]
,SUM(CASE workcode WHEN 33.03 THEN result END) AS [33_03]
FROM @t
GROUP BY sampleno
February 20, 2007 at 3:08 am
Charlie
I'm not sure how you got any results at all from that query, but never mind - try this:
--Table
CREATE TABLE MyTable (sampleno INT, workcode decimal(5, 2), result INT)
--Data
INSERT INTO MyTable VALUES (211, 33, 50)
INSERT INTO MyTable VALUES (211, 33.01, 51)
INSERT INTO MyTable VALUES (211, 33.02, 54)
INSERT INTO MyTable VALUES (211, 33.03, 32)
INSERT INTO MyTable VALUES (212, 33, 23)
INSERT INTO MyTable VALUES (212, 33.01, 34)
INSERT INTO MyTable VALUES (212, 33.02, 24)
INSERT INTO MyTable VALUES (212, 33.03, 11)
INSERT INTO MyTable VALUES (213, 33.01, 35)
INSERT INTO MyTable VALUES (214, 33, 36)
INSERT INTO MyTable VALUES (214, 33.01, 34)
INSERT INTO MyTable VALUES (214, 33.02, 45)
--Query
SELECT SampleNo,
MAX(CASE WHEN WORKCODE = 33 THEN RESULT END) AS [33_00],
MAX(CASE WHEN WORKCODE = 33.01 THEN RESULT END) AS [33_01],
MAX(CASE WHEN WORKCODE = 33.02 THEN RESULT END) AS [33_02],
MAX(CASE WHEN WORKCODE = 33.03 THEN RESULT END) AS [33_03]
FROM dbo.MyTable
GROUP BY SampleNo
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply