Query to pulldata over multiple rows

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

Query to pulldata over multiple rows

Author

Topic

tiy
Starting Member

4 Posts

Posted - 2012-08-31 : 18:08:46

Hi, I am a newbie to SQL programming and am struggling to pull data from a table like below. Lets assume the table contains two columns: Column1 and Column2.

Column1 Column2
1 A
1 B
2 A
3 A
3 B
4 B

Column1 can contain more than 1 row with same data.
Column2 can contain only data-values A or B.

I would like to have an output result like below:
Column1 Column2
1 A
1 B
3 A
3 B

That is, only those rows with column2 data with either of 'A' or 'B' while Column1 should be the same in both of the rows.

Would this be possible in SQL?

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 18:25:33

[code]
SELECT Column1,Column2
FROM
(
SELECT *,COUNT(1) OVER (PARTITION BY Column1) AS Occ
FROM Table) t
WHERE Occ > 1
[/code]

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

tiy
Starting Member

4 Posts

Posted - 2012-08-31 : 19:08:31

Hi, Thanks for the reply. But I couldn't get it to work. Could you please dissect and explain your SQL code?

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-31 : 21:36:32

[code]
select t.*
from yourtable t
inner join
(
select Column1
from yourtable
group by Column1
having count(distinct Column2) = 2
) d on t.Column1 = d.Column1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 22:43:57

quote:
Originally posted by tiy

Hi, Thanks for the reply. But I couldn't get it to work. Could you please dissect and explain your SQL code?

Thank you

why?
are you using sql 2005 or above?

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

tiy
Starting Member

4 Posts

Posted - 2012-09-01 : 13:31:35

quote:

Originally posted by khtan


select t.*
from   yourtable t
       inner join
       (
           select Column1
           from   yourtable
           group by Column1
           having count(distinct Column2) = 2
       ) d on t.Column1 = d.Column1

KH
[spoiler]Time is always against us[/spoiler]

Thanks khtan...that worked well...though i had to do soem tweaks to serve my purpose, but your code is more than enough for the job...Also served as a good learning experience ...Thanks a lot.

tiy
Starting Member

4 Posts

Posted - 2012-09-01 : 13:32:33

quote:
Originally posted by visakh16

quote:
Originally posted by tiy

Hi, Thanks for the reply. But I couldn't get it to work. Could you please dissect and explain your SQL code?

Thank you

why?
are you using sql 2005 or above?

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

hi visakh16...I am actually using oracle...thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-01 : 15:03:02

quote:
Originally posted by tiy

quote:
Originally posted by visakh16

quote:
Originally posted by tiy

Hi, Thanks for the reply. But I couldn't get it to work. Could you please dissect and explain your SQL code?

Thank you

why?
are you using sql 2005 or above?

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

hi visakh16...I am actually using oracle...thanks for your help.

then i would say you're in wrong forum. this is ms sql server forum and we deal with only t-sql
the reason Tans soln worked was because its ANSI based but we cant guarantee that will always be the case.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources