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
 subset from same table

Author  Topic 

jvst
Starting Member

12 Posts
Posted - 2012-08-04 : 09:06:57
i have a table
ITEM1 ITEM2 ITEM3
1 0 0
2 0 0
3 0 0
5 0 0
1 3 0
2 3 0
2 5 0
3 5 0
2 3 5

I have to create a table that will take each row and create a subset within the same table so that i will have 6 columns (# of orig columns times 2) with the ff data

1 2 0 0 0 0
1 3 0 0 0 0
1 5 0 0 0 0
1 1 3 0 0 0
1 2 3 0 0 0
1 2 5 0 0 0
1 3 5 0 0 0
1 2 3 5 0 0
2 1 0 0 0 0
2 3 0 0 0 0
2 5 0 0 0 0
2 1 3 0 0 0
2 2 3 0 0 0
2 2 5 0 0 0
2 3 5 0 0 0
2 2 3 5 0 0
..and so on

PLEASE, PLEASE HELP.. :(
thank you so much.

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-08-04 : 09:18:18
what is the logic here ? how do you get from 3 columns to 6 columns ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

jvst
Starting Member

12 Posts
Posted - 2012-08-04 : 09:26:01
thank you for your reply.

when you join the three items with a row with 2 items, it becomes 5 columns, .. so the maximum anticipated number of rows is twice the maximum number of items in the row.

in this example, there is only one row with 3 items (last row with items 2 3 5 but suppose you join that with another row with three items, there will be 6 items in the row .. and so on..

quote:
Originally posted by khtan

what is the logic here ? how do you get from 3 columns to 6 columns ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-08-04 : 09:33:16
quote:
when you join the three items with a row with 2 items, it becomes 5 columns

can you point out which row join with which row and what is the result ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

jvst
Starting Member

12 Posts
Posted - 2012-08-04 : 09:41:24
this is the result of the join supposedly:

1 2 0 0 0 0
1 3 0 0 0 0
1 5 0 0 0 0
1 1 3 0 0 0
1 2 3 0 0 0
1 2 5 0 0 0
1 3 5 0 0 0
1 2 3 5 0 0
2 1 0 0 0 0
2 3 0 0 0 0
2 5 0 0 0 0
2 1 3 0 0 0
2 2 3 0 0 0
2 2 5 0 0 0
2 3 5 0 0 0
2 2 3 5 0 0
..and so on

it's like i need to join the table by itself, row by row.. the problem is i don't know how to do it. this original table

1 0 0
2 0 0
3 0 0
5 0 0
1 3 0
2 3 0
2 5 0
3 5 0
2 3 5

is actually a result of merging three other tables:

table1:
1 0 0
2 0 0
3 0 0
5 0 0

table2:
1 3 0
2 3 0
2 5 0
3 5 0

table3:
2 3 5

i don't know if it will help. :(
i really hope you understand me..

quote:
Originally posted by khtan

quote:
when you join the three items with a row with 2 items, it becomes 5 columns

can you point out which row join with which row and what is the result ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-08-04 : 10:08:03
sorry, i am still lost.

can you explain how did the first row "1 2 0 0 0 0" comes about ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

jvst
Starting Member

12 Posts
Posted - 2012-08-04 : 10:44:23
the
1 2 0 0 0 0

came from joining the first row

1 0 0

with
2 0 0

quote:
Originally posted by khtan

sorry, i am still lost.

can you explain how did the first row "1 2 0 0 0 0" comes about ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

jvst
Starting Member

12 Posts
Posted - 2012-08-04 : 10:46:01
it's like

if you have:
1
2
3

you can come up with subsets like
1 2
1 3
2 1
2 3
3 1
3 2

i hope this makes more sense


quote:
Originally posted by khtan

sorry, i am still lost.

can you explain how did the first row "1 2 0 0 0 0" comes about ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-08-04 : 11:23:54
[code]
with cte (col1, col2, col3, cnt) as
(
select col1, col2, col3, cnt = sign(col1) + sign(col2) + sign(col3) from table1 union all
select col1, col2, col3, cnt = sign(col1) + sign(col2) + sign(col3) from table2 union all
select col1, col2, col3, cnt = sign(col1) + sign(col2) + sign(col3) from table3
),
cte2 as
(
select rn = row_number() over (order by cnt, col1, col2, col3),
col1, col2, col3, cnt
from cte
)
select *,
item1 = t1.col1,
item2 = case when t1.cnt = 1 then t2.col1
else t1.col2
end,
item3 = case when t1.cnt = 1 then t2.col2
when t1.cnt = 2 then t2.col1
when t1.cnt = 3 then t1.col3
end,
item4 = case when t1.cnt = 1 then t2.col3
when t1.cnt = 2 then t2.col2
when t1.cnt = 3 then t2.col1
end,
item5 = case when t1.cnt = 1 then 0
when t1.cnt = 2 then t2.col3
when t1.cnt = 3 then t2.col2
end,
item6 = case when t1.cnt = 1 then 0
when t1.cnt = 2 then 0
when t1.cnt = 3 then t2.col3
end
from cte2 t1
inner join cte2 t2 on t1.rn <> t2.rn
[/code]

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

jvst
Starting Member

12 Posts
Posted - 2012-08-04 : 11:56:05
wow.. thank you so much. but my nose is bleeding... could u please be kind enough to explain what this does? i will try to also run this on Microsoft SQL now..

quote:
Originally posted by khtan


with cte (col1, col2, col3, cnt) as
(
	select col1, col2, col3, cnt = sign(col1) + sign(col2) + sign(col3) from table1 union all
	select col1, col2, col3, cnt = sign(col1) + sign(col2) + sign(col3) from table2 union all
	select col1, col2, col3, cnt = sign(col1) + sign(col2) + sign(col3) from table3
),
cte2 as
(
	select	rn = row_number() over (order by cnt, col1, col2, col3), 
		col1, col2, col3, cnt
	from	cte
)
select	*,
	item1	= t1.col1,
	item2	= case 	when t1.cnt = 1  then t2.col1 
			else t1.col2
			end,
	item3	= case 	when t1.cnt = 1 then t2.col2
			when t1.cnt = 2 then t2.col1
			when t1.cnt = 3 then t1.col3 
			end,
	item4	= case 	when t1.cnt = 1 then t2.col3
			when t1.cnt = 2 then t2.col2 
			when t1.cnt = 3 then t2.col1
			end,
	item5	= case 	when t1.cnt = 1 then 0
			when t1.cnt = 2 then t2.col3
			when t1.cnt = 3 then t2.col2
			end,
	item6 	= case 	when t1.cnt = 1 then 0
			when t1.cnt = 2 then 0
			when t1.cnt = 3 then t2.col3
			end
from	cte2 t1
	inner join cte2 t2	on	t1.rn	<> t2.rn


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

jvst
Starting Member

12 Posts
Posted - 2012-08-04 : 12:05:45
assuming that my table is already merged like this:

1 0 0
2 0 0
3 0 0
5 0 0
1 3 0
2 3 0
2 5 0
3 5 0
2 3 5

what will i do?

coz actually table1 only has one field (item1)
table2 only has two fields (item1, item2)
table3 has three fields (item1, item2, item3)

so upon merging, i place 0 on supposedly NULL fields. so i don't know what to do with

with cte (col1, col2, col3, cnt) as
(
select col1, col2, col3, cnt = sign(col1) + sign(col2) + sign(col3) from table1 union all
select col1, col2, col3, cnt = sign(col1) + sign(col2) + sign(col3) from table2 union all
select col1, col2, col3, cnt = sign(col1) + sign(col2) + sign(col3) from table3
),
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site