Help in coalease type command - 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)

Help in coalease type command

Author

Topic

vskr72
Starting Member

1 Post

Posted - 2007-05-01 : 23:50:03

I have the following data situation:

ID,CODE,VALUE
1, A, 10
1, B, 20
2, B, 30
2, C, 40
3, C, 50
3, D, 60
4, D, 70

Ouput should looks like this:

ID,CODE,VALUE
1, A, 10
2, B, 30
3, C, 50
4, D, 9999

Rules are like this:
1. For each ID, if A is the code, then take the corresponding value in column 3.
2. If A is not found, then look for B. Take value from Col 3.
3. If B is not found, then look for C. Take value from Col 3.
4. If A or B or C is not there as CODE, then for that code, set the value as 9999 .

how do we code it in SQL. Any help is appreciated.

VSK

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-02 : 01:06:54

declare @tt table(ID int,CODE char(1),VALUE int)
insert @tt
select 1, 'A', 10 union
select 1, 'B', 20 union
select 2, 'B', 30 union
select 2, 'C', 40 union
select 3, 'C', 50 union
select 3, 'D', 60 union
select 4, 'D', 70

Select id, min(code),
case min(code )
when 'A' then coalesce(min(value), 0)
when 'B' then coalesce(min(value), 0)
when 'C' then coalesce(min(value), 0)
when 'D' then 9999 end from @tt group by id

---OR

Select max(id), code, case code
when 'A' then coalesce(max(value), 0)
when 'B' then coalesce(max(value), 0)
when 'C' then coalesce(max(value), 0)
when 'D' then 9999 end from @tt group by code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources