Special 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

General SQL Server Forums

New to SQL Server Programming

Special Query

Author

Topic

Beginer2012
Starting Member

45 Posts

Posted - 2012-05-22 : 13:01:56

Hello,
I have a the following table:

Time NumCase Code
15h00 - 16h00 1 41
15h00 - 16h00 1 42
16h00 - 17h00 3 43
17h00 - 18h00 1 44
13h00 - 14h00 2 45
14h00 - 15h00 1 46
13h00 - 14h00 1 47

Want to chage it to :
Time Period 41 42 43 44 45 46 47
13h00 - 14h00 0 0 0 0 2 0 1
14h00 - 15h00 0 0 0 0 0 1 0
15h00 - 16h00 1 1 0 0 0 0 0
16h00 - 17h00 0 0 3 0 0 0 0
17h00 - 18h00 0 0 0 1 0 0 0
Total 1 1 3 1 2 1 1 1

Any suggestions ?
Thank you

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-22 : 17:31:40

[code]

create table tab ([Time] varchar(50),NumCase int,code int)

insert into tab([Time],NumCase,code)
select '15h00 - 16h00',1,41
union all
select '15h00 - 16h00',1,42
union all
select '16h00 - 17h00',3,43
union all
select '17h00 - 18h00',1,44
union all
select '13h00 - 14h00',2,45
union all
select '14h00 - 15h00',1,46
union all
select '13h00 - 14h00',1,47

select * from tab

select [time]as [time Period],isnull([41],0)as [41],
isnull([42],0) as [42],
isnull([43],0) as [43] ,
isnull([44],0) as [44],
isnull([45],0)as [45],
isnull([46],0) as [46],
ISNULL([47],0) as [47] from (

select [time],numcase,code from tab) t
pivot( max(numcase) for code in([41],[42],[43],[44],[45],[46],[47])) Pvt

[/code]

Vijay is here to learn something from you guys.