Help with SQL

I have a table with columsn employee number, col1, col2, col3, col4, col5. Call this table 'OLDTABLE'
eg of a record:

Employee number | Col1 | Col2 | Col3 | Col4 | Col5
---------------
123455 x y z a b

Call this new table 'NEWTABLE'. I have to tranfer the data from the above table in a new one that shud look like employee number, col. the above example shud look like:

Employee number | Col
--------
123455 x
123455 y
123455 z
123455 a
123455 b

I was looking for a SQL for such a task.

ThanksINSERT INTO NEW
SELECT EmpId, Col1
FROM OLD
UNION ALL
SELECT EmpId, Col2
FROM OLD
UNION ALL
SELECT EmpId, Col3
FROM OLD
UNION ALL
SELECT EmpId, Col4
FROM OLD
UNION ALL
SELECT EmpId, Col5
FROM OLD|||thanks great brettsql

Help with query

Hi I have this table:
TABLEA
Col1 Col2
-- --
A V1
A V2
B V6
C V3
A V4
I will like
NEWTABLE
Col1 Col2
-- --
A V1, V2, V4
B V6
C V3
Just gettin once the value in Col1 and Col2 holding the
list of values assigned to Col1
Hope I made mytself clear and hope you can help, thanks a
lot!!!
PAbloPablo
You will need to write function does contacenation and returns a value
somethin like this
select id,dbo.myfunction (id)
from
(
select distinct id from mytable
) as d
"Pablo" <pablo_cr@.hotmail.com> wrote in message
news:105901c36120$b04dc3c0$a001280a@.phx.gbl...
> Hi I have this table:
> TABLEA
> Col1 Col2
> -- --
> A V1
> A V2
> B V6
> C V3
> A V4
> I will like
> NEWTABLE
> Col1 Col2
> -- --
> A V1, V2, V4
> B V6
> C V3
> Just gettin once the value in Col1 and Col2 holding the
> list of values assigned to Col1
> Hope I made mytself clear and hope you can help, thanks a
> lot!!!
> PAblo|||Hi Pablo,
If you just need to do this one time to populate the
NEWTABLE from TABLEA, the following script should work:
declare @.col1 varchar(5),
@.maxCol1 varchar(5),
@.col2 varchar(25)
set @.maxCol1 = (select max(col1) from tablea)
set @.col1 = (select min(col1) from tablea)
while @.col1 <= @.maxCol1
begin
-- initialize
set @.col2 = ''
-- build concatenated list of values
select @.col2 = @.col2 + col2 + ', '
from tablea
where col1 = @.col1
-- just remove the trailing comma
set @.col2 = substring(@.col2, 1, len(@.col2) - 1 )
-- populate new table
insert into newtable (col1, col2)
values(@.col1, @.col2)
set @.col1 = (select min(col1) from tablea where col1 >
@.col1)
end
HTH,
John Lennox
>--Original Message--
>Hi I have this table:
>TABLEA
>Col1 Col2
>-- --
>A V1
>A V2
>B V6
>C V3
>A V4
>I will like
>NEWTABLE
>Col1 Col2
>-- --
>A V1, V2, V4
>B V6
>C V3
>Just gettin once the value in Col1 and Col2 holding the
>list of values assigned to Col1
>Hope I made mytself clear and hope you can help, thanks a
>lot!!!
>PAblo
>.
>