Sql

Hi,

I have data like this:

Appid | CID | Level

1| 100| a

1|101 | null

1 |102 |null

2 |100 | null

2 | 101 |b

2 | 102 | null

3 |100 | null

3 | 101 | null

3 | 102 | c

I have only 3 values for the CID column. 100, 101, 102.

I need to get result like this:

AppID | L1( Level with CID 100) | L2 (Level with CID 101) | L3 (Level With CID 103)

1 anull null

2 null b null

3 nullnull c

How to get this result?

Thanks in advance

April 4th, 2008 9:42pm

Here's a query that will work for you:

Code Snippet

-- Set up sample data

declare @data table (AppID int, CID int, Level nvarchar(10))

insert into @data

select 1, 100, 'a'

union all select 1, 101, null

union all select 1, 102, null

union all select 2, 100, null

union all select 2, 101, 'b'

union all select 2, 102, null

union all select 3, 100, null

union all select 3, 101, null

union all select 3, 102, 'c'

-- Query starts here (SQL Server 2005 and later - uses PIVOT)

select AppID

, [100] as 'L1 (Level with CID 100)'

, [101] as 'L2 (Level with CID 101)'

, [102] as 'L3 (Level with CID 102)'

from @data

pivot (min(Level) for CID in ([100], [101], [102])) pvt

-- Alternative query starts here (all versions)

select AppID

, min(case when CID = 100 then Level else null end) as 'L1 (Level with CID 100)'

, min(case when CID = 101 then Level else null end) as 'L2 (Level with CID 101)'

, min(case when CID = 102 then Level else null end) as 'L3 (Level with CID 102)'

from @data

group by AppID

There's two versions presented, depending on which version of SQL Server you're using.

Iain

Free Windows Admin Tool Kit Click here and download it now
April 5th, 2008 5:48am

I have solved similar kind of problem in my blog.

Please check it,

http://venkattechnicalblog.blogspot.com/2008/04/t-sql-challenges-part-1.html

Regards,

Venkatesan Prabu . J

April 5th, 2008 10:28am

Dear Mahima

I am using Pivot to do that

http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query

Best Regards

mintssoul

Free Windows Admin Tool Kit Click here and download it now
April 9th, 2015 12:27am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics