May 12, 2011 at 5:29 am
Hi All,
I have a requirement in sql & sybase.
create table b_req
(
b_id int,
ac_id int,
type varchar(30)
)
insert into b_req
select 1,100,'AGGREGATE'
union
select 1,100,'FEE'
union
select 2,500,'AGGREGATE'
union
select 2,500,'FEE'
union
select 3,450,'AGGREGATE'
union
select 3,450,'FEE'
union
select 4,750,'AGGREGATE'
union
select 5,150,'AGGREGATE'
union
select 5,150,'FEE'
Expected Output:
b_id ac_id AGGREGATE FEE
1 100 Y Y
2 500 Y Y
3 450 Y Y
4 750 Y N - It doesn't have any row for FEE
5 150 Y Y
Inputs are welcome!
karthik
May 12, 2011 at 6:23 am
I have tried the below query. It gives the expected result. is there any other way to do the same?
select b_id
,ac_id
,'AGGREGATE' = case when min(type) = 'AGGREGATE' then 'Y' else 'N' end
,'FEE' = case when max(type) = 'FEE' then 'Y' else 'N' end
from b_req
group by b_id
,ac_id
karthik
May 12, 2011 at 10:25 am
Here is a slight variation which will protect you against new "type" values added to your database down the line from altering your expected results:
Yours (re-formatted):
SELECT b_id,
ac_id,
'AGGREGATE' = CASE WHEN MIN(type) = 'AGGREGATE' THEN 'Y'
ELSE 'N'
END,
'FEE' = CASE WHEN MAX(type) = 'FEE' THEN 'Y'
ELSE 'N'
END
FROM b_req
GROUP BY b_id,
ac_id
Mine:
SELECT b_id,
ac_id,
'AGGREGATE' = MAX(CASE WHEN type = 'AGGREGATE' THEN 'Y'
ELSE 'N'
END),
'FEE' = MAX(CASE WHEN type = 'FEE' THEN 'Y'
ELSE 'N'
END)
FROM b_req
GROUP BY b_id,
ac_id
Notice that my CASE is evaluating "type =" instead of "MAX(type) =" or "MIN(type) =".
To test run this insert and then run the two queries again:
INSERT INTO b_req
SELECT 1,
100,
'A_AGGREGATE'
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply