| Author |
 Topic  |
|
|
rajsqlteam
Starting Member
14 Posts |
 Posted - 12/19/2012 : 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
India
47023 Posts |
Posted - 12/19/2012 : 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/
|
 |
|
|
rajsqlteam
Starting Member
14 Posts |
Posted - 12/19/2012 : 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 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/19/2012 : 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; |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 12/19/2012 : 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/
|
|
|
|
rajsqlteam
Starting Member
14 Posts |
Posted - 12/19/2012 : 10:11:07
|
| Thank you! it worked. you guys rock! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 12/19/2012 : 10:36:38
|
just out of curiosity, which was your final solution?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
rajsqlteam
Starting Member
14 Posts |
Posted - 12/19/2012 : 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
India
47023 Posts |
Posted - 12/19/2012 : 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/
|
|
|
|
rajsqlteam
Starting Member
14 Posts |
Posted - 12/19/2012 : 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
India
47023 Posts |
Posted - 12/19/2012 : 21:56:03
|
both 1 and 3 or either of 1 and 3?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
how to query below example
