SQL 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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Query

Author

Topic

charles101
Starting Member

16 Posts

Posted - 2007-02-20 : 02:31:09

Hello can someon 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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-20 : 02:45:10

[code]-- prepare sample data
declare @t table (sampleno int, workcode varchar(5), result int)

insert @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

-- show the data
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 @t
group by sampleno
order by sampleno[/code]

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-20 : 02:45:34

[code]
declare @yourtable table
(
sampleno int,
workcode decimal(10,2),
result int
)
insert into @yourtable
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

select sampleno,
[33.00] = sum(case when workcode = 33.00 then result end),
[33.01] = sum(case when workcode = 33.01 then result end),
[33.02] = sum(case when workcode = 33.02 then result end),
[33.03] = sum(case when workcode = 33.03 then result end)
from @yourtable
group by sampleno
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-20 : 02:52:26

Only a mere 24 seconds...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources