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
 how to query below example

Author  Topic 

rajsqlteam
Starting Member

14 Posts

Posted - 2012-12-19 : 09:57:57

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

52326 Posts

Posted - 2012-12-19 : 10:01:25
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 - 2012-12-19 : 10:04:37
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-19 : 10:05:12
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

52326 Posts

Posted - 2012-12-19 : 10:05:28
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 - 2012-12-19 : 10:11:07
Thank you! it worked. you guys rock!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 10:36:38
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 - 2012-12-19 : 10:56:22
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

52326 Posts

Posted - 2012-12-19 : 11:02:39
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 - 2012-12-19 : 11:26:21
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

52326 Posts

Posted - 2012-12-19 : 21:56:03
both 1 and 3 or either of 1 and 3?

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

Go to Top of Page
   

- Advertisement -