November 10, 2009 at 7:12 am
Hi all,
Here is the case. I want to create a kind of an view where I present data from a table.
table1 (Existing) / main columns presented
seq
item
main
sub
...
..
.
I Want to move main, sub column to a new table or a view.
CREATE TABLE custom.table2 (
obj_num INT NOT NULL DEFAULT AUTOINCREMENT,
main CHAR (50),
sub CHAR (50)
)
After this I insert the values to table2 from table1
INSERT INTO custom.table2 (main, sub)
SELECT main, sub
FROM custom.table1
The result looks like this now.
1, main1, sub1
2, main1, sub2
3, main1, sub3
4, main2, sub1
..and so on
I Want in organize this table by DISTINCT main followed by sub i one row. Little unsecue how to create this, have been thinking about using ALTER TABLE, UNION and GROUP BY. Don´t know if this is the right way to go.
1, main1
2, sub1
3, sub3
4, sub3
5, main2
6, sub1
..and so on
Next step below:
If this is possible. Can I reorganize and update obj_num column and sort this by the main and sub. If i want every main to start by the number '1000' and followed by linked sub straight after ?
The column main and sub could variate a lot in numbers. So I can´t preconfigure the different main and sub to a new specific obj_num.
1000, main1
1001, sub1
1002, sub2
1003, sub3
2000, main2
2001, sub1
..and so on
Regards
November 11, 2009 at 12:56 am
Hi,
Try this
CREATE TABLE #table2
(
main CHAR (10),
sub CHAR (10)
)
insert into #table2
select 'main1','sub1'
union all
select 'main1','sub2'
union all
select 'main1','sub3'
union all
select 'main2','sub4'
union all
select 'main2','sub5'
union all
select 'main3','sub6'
union all
select 'main3','sub7'
union all
select 'main3','sub8'
--GROUP TABLE #main
select identity (int,1,1) slno,main into #main
from #table2
group by main
--ROWCOUNT TABLE #sub
select *,(select count(*) from #table2 b where
b.main = a.main
and b.sub <= a.sub)as rowNumber into #sub
from #table2 a
--FINAL
select (slno *1000)slno ,main from #main
union all
select (b.slno*1000)+a.rowNumber,a.sub from #sub a,#main b
where a.main = b.main
order by 1
RESULT
slno|main
1000|main1
1001|sub1
1002|sub2
1003|sub3
2000|main2
2001|sub4
2002|sub5
3000|main3
3001|sub6
3002|sub7
3003|sub8
November 11, 2009 at 12:24 pm
Hi,
Thx for quick reply 🙂
Gonna try it right away. Get back with the result.
Regards
November 16, 2009 at 12:52 am
arun.sas:
This worked fine. Thx a lot 🙂
What if I want to create a table och the result of the union statement ?
Hade read some about pivot/cross table. Don´t know if this is the case for this scenario ?
Regards.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply