SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 union
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

whitefreesia.bai
Starting Member

6 Posts

Posted - 08/23/2013 :  12:37:58  Show Profile  Reply with Quote
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




Edited by - whitefreesia.bai on 08/23/2013 13:38:19

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 08/23/2013 :  14:42:49  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/24/2013 :  11:26:14  Show Profile  Reply with Quote

        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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000