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 Sourc761, 1, 1757, 1, 8761, 2, 3Desired O/P:ID, Ful761, 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 tableGROUP BY ID ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 workingselect distinct ID , (CASE WHEN SOURCES_C = 1 AND SOURCE = 3) ) THEN '1'ELSE '0' END) AS o/p from HH |
|
|
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 ENDFROM TblGROUP BY id; |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
rajsqlteam
Starting Member
14 Posts |
Posted - 2012-12-19 : 10:11:07
|
Thank you! it worked. you guys rock! |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|