SQL Query help!!! - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Query help!!!

Author

Topic

hiral265
Starting Member

1 Post

Posted - 2010-01-11 : 14:22:58

Hi,

I have a table like this:

Col1 Col2 Col3
1 2 A
1 2 B
1 2 C
1 3 A
1 3 B
1 4 C
1 5 A

I want to insert rows in this table such that for every combination of col1 and col2, I have all distinct set of values of col3 i.e. my final table should look like:

Col1 Col2 Col3
1 2 A
1 2 B
1 2 C
1 3 A
1 3 B
1 3 C
1 4 A
1 4 B
1 4 C
1 5 A
1 5 B
1 5 C

Notice that for every combination of col1 and col2 now I have 3 rows with A, B and C as the values in Col3.

How do I achieve this with a query? And without using cursors.

Any help would be greatly appreciated.

Thanks

shan
Yak Posting Veteran

84 Posts

Posted - 2010-01-11 : 14:38:00

Does this work

declare @tbl as table (col1 varchar(2),col2 varchar(2), col3 varchar(2))
insert into @tbl
select '1','2','B'
union all
select '1','2','C'
union all
select '1','3','A'
union all
select '1','3','B'
union all
select '1','4','C'
union all
select '1','5 ','A'

select distinct col1,col2,B.col3 from @tbl A
cross apply
(select col3 from @tbl ) as B

-Shan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-11 : 14:38:23

Try using a CROSS JOIN.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-11 : 14:38:59

[code]-- prepare testdata
declare @yak table (col1 int, col2 int, col3 char(1))
insert @yak
select 1, 2, 'A' union all
select 1, 2, 'B' union all
select 1, 2, 'C' union all
select 1, 3, 'A' union all
select 1, 3, 'B' union all
select 1, 4, 'C' union all
select 1, 5, 'A'

-- show testdata
select * from @yak

-- solution using testdata
insert @yak
select distinct y1.col1,y1.col2,y2.col3
from @yak y1
join
(select distinct col3 from @yak) y2 on 1=1
where not exists (select * from @yak where col1 = y1.col1 and col2 = y1.col2 and col3 = y2.col3 )

-- show result
select * from @yak order by col1,col2,col3[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 01:09:07

quote:
Originally posted by hiral265

Hi,

I have a table like this:

Col1 Col2 Col3
1 2 A
1 2 B
1 2 C
1 3 A
1 3 B
1 4 C
1 5 A

I want to insert rows in this table such that for every combination of col1 and col2, I have all distinct set of values of col3 i.e. my final table should look like:

Col1 Col2 Col3
1 2 A
1 2 B
1 2 C
1 3 A
1 3 B
1 3 C
1 4 A
1 4 B
1 4 C
1 5 A
1 5 B
1 5 C

Notice that for every combination of col1 and col2 now I have 3 rows with A, B and C as the values in Col3.

How do I achieve this with a query? And without using cursors.

Any help would be greatly appreciated.

Thanks


insert into table
select t.col1,t1.col2,t2.col3
from (select distinct col1,col2 from table)t1
cross join (select distinct col3 from table)t2
left join table t3
on t1.col1=t3/col1
and t1.col2=t3.col2
and t2.col3=t3.col3
where t3.col1 is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources