to display horizontally in cursor

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

SQL Server Development (2000)

to display horizontally in cursor

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2009-10-08 : 01:57:31

Hi,

create table #temp1
(
application varchar(100),
insertdate varchar(20),
quantity int)

insert into #temp1 values ('adobe 1.1','2009-09-02',34)
insert into #temp1 values ('adobe 1.2','2009-09-02',45)
insert into #temp1 values ('adobe 1.1','2009-09-09',100)
insert into #temp1 values ('adobe 1.2','2009-09-09',78)
insert into #temp1 values ('adobe 1.1','2009-09-16',900)
insert into #temp1 values ('adobe 1.2','2009-09-16',58)

declare @devicename varchar(1000)
DECLARE device_cursor CURSOR FOR
SELECT distinct insertdate from #temp1
Order By insertdate
open device_cursor
FETCH NEXT FROM device_cursor
INTO @devicename

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
Select application,
max(case when insertdate = @devicename then quantity else 0 end) as +''+@devicename+''+
from #temp1
group by application

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM device_cursor
INTO @devicename
END

CLOSE device_cursor
DEALLOCATE device_cursor
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-08 : 02:42:02

What is your expected result?

Madhivanan

Failing to plan is Planning to fail

sachingovekar
Posting Yak Master

101 Posts

Posted - 2009-10-08 : 02:49:50

output should be

OUTPUT SHOULD BE
-----------------------------------------------------------
APPLICATION 2009-09-02 2009-09-09 2009-09-16
------------------------------------------------------------
adobe 1.1 34 100 900
adobe 1.2 45 78 58
------------------------------------------------------------

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-08 : 04:07:09

Use this
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources