How to get a resultset like this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to get a resultset like this?

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-12-08 : 00:39:47

Hi guys,

I have a table structure as follows.

Declare @tbl as table(instid int,year int,month varchar(30),day int,type1 int,type2 int,type3 int)
insert into @tbl
select 317, 2008, 'December', 1, 1258, NULL, NULL union all
select 317, 2008, 'December', 1, 1099, NULL, NULL union all
select 317, 2008, 'December', 1, 2, NULL, NULL union all
select 317, 2008, 'December', 1, 1088, NULL, NULL union all
select 317, 2008, 'December', 1, 1237, NULL, NULL union all
select 317, 2008, 'December', 1, 1274, NULL, NULL union all
select 318, 2008, 'December', 1, NULL, 3270.8, NULL union all
select 318, 2008, 'December', 1, NULL, 2824.43,NULL union all
select 318, 2008, 'December', 1, NULL, 0, NULL union all
select 318, 2008, 'December', 1, NULL, 2937.6, NULL union all
select 318, 2008, 'December', 1, NULL, 3488.34,NULL union all
select 318, 2008, 'December', 1, NULL, 3554.46,NULL union all
select 319, 2008, 'December', 1, NULL, NULL, 0 union all
select 319, 2008, 'December', 1, NULL, NULL, 0 union all
select 319, 2008, 'December', 1, NULL, NULL, 0 union all
select 319, 2008, 'December', 1, NULL, NULL, 0 union all
select 319, 2008, 'December', 1, NULL, NULL, 0 union all
select 319, 2008, 'December', 1, NULL, NULL, 0

Is there a way to get the result set this way?Actually the above is a part of a resultset of some other query?But I want to get the resultset as below.

2008 December 1 1258 3270.8 0 0
2008 December 1 1099 2824.43 0 0
2008 December 1 2 0 0 0
2008 December 1 1088 2937.6 0 0
2008 December 1 1237 3488.34 0 0
2008 December 1 1274 3554.46 0 0

Thanks for any help.

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-12-08 : 01:44:44

How do you know (programatically) that 1258 goes with 3270.8 ?

Are you relying on the order of insert or is there some other Id that we can use?

Nathan Skerl

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-12-08 : 01:59:38

As you can see there is a column called instid .Since for instid 317 the first reading is 1258 and for type2 the first reading for 1st December is 3270.8 it will go together same for the second reading I know it sounds dumb but this is what I want.

2008 December 1 1258 3270.8 0
2008 December 1 1099 2824.43 0
2008 December 1 2 0 0
2008 December 1 1088 2937.6 0
2008 December 1 1237 3488.34 0
2008 December 1 1274 3554.46 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 03:04:30

[code]Declare @tbl as table(instid int,year int,month varchar(30),day int,type1 decimal(10,2),type2 decimal(10,2),type3 decimal(10,2))
insert into @tbl
select 317, 2008, 'December', 1, 1258, NULL, NULL union all
select 317, 2008, 'December', 1, 1099, NULL, NULL union all
select 317, 2008, 'December', 1, 2, NULL, NULL union all
select 317, 2008, 'December', 1, 1088, NULL, NULL union all
select 317, 2008, 'December', 1, 1237, NULL, NULL union all
select 317, 2008, 'December', 1, 1274, NULL, NULL union all
select 318, 2008, 'December', 1, NULL, 3270.8, NULL union all
select 318, 2008, 'December', 1, NULL, 2824.43,NULL union all
select 318, 2008, 'December', 1, NULL, 0, NULL union all
select 318, 2008, 'December', 1, NULL, 2937.6, NULL union all
select 318, 2008, 'December', 1, NULL, 3488.34,NULL union all
select 318, 2008, 'December', 1, NULL, 3554.46,NULL union all
select 319, 2008, 'December', 1, NULL, NULL, 0 union all
select 319, 2008, 'December', 1, NULL, NULL, 0 union all
select 319, 2008, 'December', 1, NULL, NULL, 0 union all
select 319, 2008, 'December', 1, NULL, NULL, 0 union all
select 319, 2008, 'December', 1, NULL, NULL, 0 union all
select 319, 2008, 'December', 1, NULL, NULL, 0

SELECT Seq,year,month,day,
SUM(CASE WHEN instid=317 THEN type1 ELSE NULL END) AS type1,
SUM(CASE WHEN instid=318 THEN type2 ELSE NULL END) AS type2,
SUM(CASE WHEN instid=319 THEN type3 ELSE NULL END) AS type3
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY instid ORDER BY instid) AS Seq,*
from @tbl

)t
GROUP BY Seq, year,month,day

output
-----------------------------------------------

Seq year month day type1 type2 type3
1 2008 December 1 1258.00 3270.80 0.00
2 2008 December 1 1099.00 2824.43 0.00
3 2008 December 1 2.00 0.00 0.00
4 2008 December 1 1088.00 2937.60 0.00
5 2008 December 1 1237.00 3488.34 0.00
6 2008 December 1 1274.00 3554.46 0.00

[/code]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-12-08 : 04:27:17

Thanks for the reply.But the instid wont be the same.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 05:58:58

then what determines which values need to be merged? can you provide some data to show your exact scenario?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources