July 30, 2010 at 3:30 am
I have a table having data in following format:
COURSECODETYPE
A L
B P
B L
C P
C P
C T
D P
E NULL
F NULL
G NULL
H NULL
I NULL
J NULL
I need a query to get the result in following format:
COURSECODE L T P
A 1 0 0
B 1 0 1
C 0 1 1
D 0 0 1
E 0 0 0
F 0 0 0
G 0 0 0
H 0 0 0
I 0 0 0
J 0 0 0
Please help in desgining the query
CourseCodes count is not fixed
July 30, 2010 at 3:44 am
Try this:
declare @tab table
(
ccode varchar(2),
ctype varchar(2)
)
insert into @tab
select 'A', 'L'
union all select 'A' , 'P'
union all select 'B' , 'P'
union all select 'B' , 'L'
union all select 'B' , 'T'
union all select 'C' , NULL
union all select 'D' , NULL
select cCOde,
CASE WHEN [L] = 'L' THEN 1 ELSE 0 END L,
CASE WHEN [T] = 'T' THEN 1 ELSE 0 END T,
CASE WHEN [P] = 'P' THEN 1 ELSE 0 END P
From
(select * from @tab ) pivot_table
PIVOT
(MAX(ctype) FOR ctype in ([T],[P],[L])) pivot_handle
Please provide us ready to use sample scripts to work with the next time.. it will save us a lot of time and concentrate on the T-SQL directly..
Hope this helps!
~Edit : dint see the actual requirement; now edited the code to match the Actual request.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply