August 21, 2008 at 1:16 am
Dear Pals ,
I have a small requirement, I have two tables "test" and "trg"
drop table test
create table test
( col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10)
)
insert into test
select 'a','b','c','d'
union all
select 'e','f','g','h'
select * from test
-- Output
/*
col1col2col3col4
abcd
efgh
*/
I have one more table
create table trg
(id int identity(1,1),
col varchar(10)
)
I need to data to be loaded into "trg" table as below
select * from trg
id col
-------
1a
2b
3c
4d
5e
6f
7g
8h
Can anyone help me out?
Thanks in Advance.
August 21, 2008 at 2:11 am
Assuming that you want to maintain the ordering, i.e. all the first rows have lower id than the next rows (and I assume the row ordering is by the col1), then you can do:
insert into trg select col1 from
(select col1, ROW_NUMBER()over (order by col1) *4 as nr from test
union all
select col2, ROW_NUMBER() over (order by col1) *4 +1 as nr from test
union all
select col3, ROW_NUMBER() over (order by col1) *4 +2 as nr from test
union all
select col4, ROW_NUMBER() over (order by col1) *4 +3 as nr from test) as x
order by nr
select * from trg
If the order does not matter, then just do a series of selects 🙂 either in sequence or union all them together.
Regards,
Andras
August 21, 2008 at 2:13 am
PS: thanks for providing table definition, test data and expected results in your question, hope this will get you the best & right answer 🙂
- Andras
August 21, 2008 at 2:46 am
Thank You!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply