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
>.
>