union - 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

union

Author

Topic

whitefreesia.bai
Starting Member

6 Posts

Posted - 2013-08-23 : 12:37:58

Hi,

Could someone revise my sql code so that it can run faster? Thank you very much.
I have a table with id and cat. There may be multiple rows for the same ID with different cat. cat only has two values 'a' or 'b'. I want to create a new_cat column that has values of 'a only', 'a+b' and 'b only'. The following is my code

with a_only as (
select distinct id, 'a only' as new_cat
from database
where cat = 'a' and id not in (
select distinct id
from database
where cat = 'b'
)
),

a_n_b as (
select distinct id, 'a+b' as new_cat
from database
where cat = 'a' and id in (
select distinct id
from database
where cat = 'b'
)
),

b_only as (
select distinct id, 'b only' as new_cat
from database
where cat = 'b' and id not in (
select distinct id
from database
where cat = 'a'
)
)

------ union three sql -----
select * from a_only
union
select * from a_n_b
union
select * from b_only

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-23 : 14:42:49

[code]SELECT
id,
CASE
WHEN A = 1 AND B=1 THEN 'a+b'
WHEN A = 1 THEN 'a'
WHEN b = 1 THEN 'b'
END AS new_cat
FROM
(
SELECT
id,
MAX(CASE WHEN cat='a' THEN 1 ELSE 0 END) AS A,
MAX(CASE WHEN cat='b' THEN 1 ELSE 0 END) AS B
FROM
database
GROUP BY
id
) S[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-24 : 11:26:14

[code]
SELECT id,
CASE WHEN MAX(Cat) = 'a' THEN 'a only'
WHEN COUNT(DISTINCT Cat) = 2 THEN 'a + b'
ELSE 'b only'
END
FROM database
WHERE Cat IN ('a','b')
GROUP BY id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources