using 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 2005 Forums

Transact-SQL (2005)

using pivot

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2007-07-25 : 15:27:23

Below is the query that works well and I want to add new rows to result set.

Create table #Test(BatchID int ,Status int)

insert into #test select 1001 ,1
insert into #test select 1001 ,2
insert into #test select 1002 ,0
insert into #test select 1002 ,3
insert into #test select 1002 ,4
insert into #test select 1003 ,4
insert into #test select 1004 ,4

declare @Status varchar(50)
set @status = ''

Select @Status = @Status + '[' + convert(varchar(2),a.status )+ '],' from (Select distinct a.Status from #Test a) a

SET @Status = LEFT(@Status, LEN(@Status) - 1)

exec('SELECT * FROM #test p PIVOT( count(Status) for status in (' + @Status + ')) AS pvt ORDER BY BatchId')
drop table #test

what I am looking for is to add new rows to this resultset.if you look at the result below the i want new rows newid, newid1 with 0's under all the columns. The batchids are not in the temp table they come for different table.
The result set shld look like this.

0 1 2 3 4
------------------------
1001 0 1 1 0 0
1002 1 0 0 1 1
1003 0 0 0 0 1
1004 0 0 0 0 1
newid 0 0 0 0 0
newid2 0 0 0 0 0

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-25 : 15:35:38

Add "UNION ALL SELECT BatchId, 0, 0, 0, 0, 0 FROM YourBatchTable" to your SELECT statement in the EXEC.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

cognos79
Posting Yak Master

241 Posts

Posted - 2007-07-25 : 15:43:38

I dont know how many columns I have in the resultset. It changes everytime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources