Pivot ? - 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)

Pivot ?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-08 : 08:02:50

Declan writes "Please run sample below.

What I am trying to achieve is a single row as the result. I show a "workaround" at the bottom. Is ther an easier/better way?

Thanks,
Declan Ward

AwaitingCheck AwaitingResults
==============||=================
AA BB

Is this possible?

drop table #temp
create table #temp
(
jobnumber char(10),
StatusID int,
Initials char(5)
)
GO
insert into #temp values ('04G00020', 1, 'AA')
insert into #temp values ('04G00020', 2, '')
insert into #temp values ('04G00020', 3, '')
insert into #temp values ('04G00020', 4, 'BB')
insert into #temp values ('04G00020', 5, '')
insert into #temp values ('04G00020', 6, '')
insert into #temp values ('04G00021', 1, '')
insert into #temp values ('04G00021', 2, '')
insert into #temp values ('04G00021', 3, '')
insert into #temp values ('04G00021', 4, '')
insert into #temp values ('04G00021', 5, '')
insert into #temp values ('04G00021', 6, '')
GO

select * from #temp
where (JobNumber = '04g00020')AND (StatusID = 1 OR
StatusID = 4)
GO
Select
(case StatusID WHEN 1 then Initials end) As AwaitingCheck,
(case StatusID WHEN 4 then Initials end) As AwaitingResults
FROM #temp
where
(JobNumber = '04g00020')AND (StatusID = 1 OR
StatusID = 4)
GO

This is my workaround: Is there a better way???
====================================

--Workaround !!!

create table #temp
(
jobnumber char(10),
StatusID int,
Initials char(5)
)
GO
insert into #temp values ('04G00020', 1, 'AA')
insert into #temp values ('04G00020', 2, '')
insert into #temp values ('04G00020', 3, '')
insert into #temp values ('04G00020', 4, 'BB')
insert into #temp values ('04G00020', 5, '')
insert into #temp values ('04G00020', 6, '')
insert into #temp values ('04G00021', 1, '')
insert into #temp values ('04G00021', 2, '')
insert into #temp values ('04G00021', 3, '')
insert into #temp values ('04G00021', 4, '')
insert into #temp values ('04G00021', 5, '')
insert into #temp values ('04G00021', 6, '')
GO

DECLARE check_cursor CURSOR
FOR
SELECT Initials
FROM #temp
WHERE (JobNumber = '04g00020')AND (StatusID = 1)

OPEN check_cursor
DECLARE @CheckInitials char(5)

FETCH NEXT FROM check_cursor INTO @CheckInitials

DECLARE result_cursor CURSOR
FOR
SELECT Initials
FROM #temp
WHERE (JobNumber = '04g00020')AND (StatusID = 4)

OPEN result_cursor
DECLARE @ResultInitials char(5)

FETCH NEXT FROM result_cursor INTO @ResultInitials

create table #temp2
(
AwaitingCheck char(5),
AwaitingResult char(5)
)

insert into #temp2 values (@CheckInitials, @ResultInitials)

CLOSE check_cursor
CLOSE result_cursor
DEALLOCATE check_cursor
DEALLOCATE result_cursor

Select * from #temp2
GO

drop table #temp
drop table #temp2

GO"

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-06-08 : 14:32:57

Sorted!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-06-08 : 15:20:24

[code]Select
MAX((case StatusID WHEN 1 then Initials end)) As AwaitingCheck,
MAX((case StatusID WHEN 4 then Initials end)) As AwaitingResults
FROM #temp
where
(JobNumber = '04g00020')AND (StatusID = 1 OR
StatusID = 4)
GO
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources