You can do it in two SQL statements if you use characteristic functions.
First you have to generate a temp table in which you number the rows 1...n
for each fooval per key in foo. That is one SQL statement. Then, using that
resulting table, you use the characteristic function for varchar fields and
flip the table, joining the fields in the process. Just be aware that
in this step, you need as many or more of the "max" functions as the max number
of foovals per key in foo originally. In your example, you had a max of 4
foovals per key (key=1), but I used 5 "max" functions just for fun.
Statement two handles NULLs so you can add more than you need and it will
still work correctly. Just follow the pattern and increment the integer
subtracted from "id" for each new "max" function you add.
/*
this numbers each row uniquely per each key
*/
select id=count(*), x.key, x.fooval
into #foo2
from foo x, foo y
where (y.fooval <= x.fooval and x.key=y.key )
group by x.key,x.fooval
order by x.fooval
go
/*
this flips the table and concatanates the appropriate foovals
*/
select key,
cval1 =
max (substring ( fooval, 1, datalength(fooval) *
(1-abs(sign(id-1))))) + ' ' +
max (substring ( fooval, 1, datalength(fooval) *
(1-abs(sign(id-2))))) + ' ' +
max (substring ( fooval, 1, datalength(fooval) *
(1-abs(sign(id-3))))) + ' ' +
max (substring ( fooval, 1, datalength(fooval) *
(1-abs(sign(id-4))))) + ' ' +
max (substring ( fooval, 1, datalength(fooval) *
(1-abs(sign(id-5)))))
from
#foo2
group by key
go
Quote:> Hi, all
> I have a table like that:
> create table foo ( key int, fooval varchar(10) )
> with contents like that:
> key fooval
> 1 123
> 1 1111
> 1 abc
> 1 aabc
> 2 zzz
> 2 a
> 2 z
> ................................
> I want to populate another table:
> create table bar ( key int, barval varchar(255)
> so that it will look like that:
> key barval
> 1 1111 123 aabc abc .
> 2 a z zzz .
> ..........................
> with with 'subvalues' in barval ASCII-sorted.
> My question is:
> Can this be done without cursors or any other row-by-row
> processing?
> I'm currently using MS SQL Server version 4.2, but we're
> planning to upgrade to 6.5 soon, so I'm open to using
> 6.x-specific features like IDENTITY.
> TIA
> Isaac
by