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
 how to query below example
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rajsqlteam
Starting Member

14 Posts

Posted - 12/19/2012 :  09:57:57  Show Profile  Reply with Quote

Hi I have table as shown below, I want to query distinct rows that has a value of 1 and 3 and put '1' in the resultant tabe as shown. But the problem is there is another column in the source table 'LINE' which is making my query repeating ID's. Irrespective of the line number If I have the values a and 3 in the source table it should show 1 in the output. Please help!
Source table:
ID, LINE Sourc
761, 1, 1
757, 1, 8
761, 2, 3

Desired O/P:
ID, Ful
761, 1
757, 0

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/19/2012 :  10:01:25  Show Profile  Reply with Quote
do you mean this?

SELECT ID,
MAX(CASE WHEN Sourc IN (1,3) THEN 1 ELSE 0 END) AS [O/P]
FROM table
GROUP BY ID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rajsqlteam
Starting Member

14 Posts

Posted - 12/19/2012 :  10:04:37  Show Profile  Reply with Quote
The above one has OR and it brings 2 rows with same ID since it has two different line numbers.
I was trying something like this, but its obv not working
select distinct ID ,
(CASE WHEN SOURCES_C = 1 AND SOURCE = 3) ) THEN '1'
ELSE '0' END) AS o/p
from HH
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/19/2012 :  10:05:12  Show Profile  Reply with Quote
May be this then?
SELECT
	id,
	CASE WHEN
		SUM(DISTINCT CASE WHEN sourc = 1 THEN 1 WHEN sourc = 3 THEN 2 ELSE 0 END) = 3 THEN 1
		ELSE 0
	END
FROM
	Tbl
GROUP BY
	id;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/19/2012 :  10:05:28  Show Profile  Reply with Quote
thats why you need to use GROUP BY as i showed so that ID wont repeat

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rajsqlteam
Starting Member

14 Posts

Posted - 12/19/2012 :  10:11:07  Show Profile  Reply with Quote
Thank you! it worked. you guys rock!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/19/2012 :  10:36:38  Show Profile  Reply with Quote
just out of curiosity, which was your final solution?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rajsqlteam
Starting Member

14 Posts

Posted - 12/19/2012 :  10:56:22  Show Profile  Reply with Quote
I actually tried with group by function but forgot to mention above, But Sunitabeck logic worked fine.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/19/2012 :  11:02:39  Show Profile  Reply with Quote
so you want 1 to be returned only if you've 1,3 and any other values present in sourc for same id?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rajsqlteam
Starting Member

14 Posts

Posted - 12/19/2012 :  11:26:21  Show Profile  Reply with Quote
I am looking for only values having 1,3..other values how it as 0
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/19/2012 :  21:56:03  Show Profile  Reply with Quote
both 1 and 3 or either of 1 and 3?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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.06 seconds. Powered By: Snitz Forums 2000