Question with Query, Loop?

[Johnblaze1]

I have data in a format such as;
NAME SEQ POST
Fred 01 Hello and
Fred 02 Welcome

I would like to have the output show as;
NAME POST
Fred Hello and Welcome

So that all values in column POST are concatenated for output. Is this possible with SQL? Otherwise, I am going to have to dump the data and write a small VB procedure to do this.

Any help would be greatly appreciated

Jon

 

[ddiamond]

I don't think you can do that with straight sql. You could do it with a stored procedure, but that would be similar to your VB solution.

 

[BrianTyler]

Something like this could work, but only if there are a small number of rows involved:

select
Name
,P1||' '||P2||' '||P3||' '||P4 as Post
from
(select
a.Name as Name
,a.Post as P1
,rtrim(coalesce(b.Post,'')) as P2
,rtrim(coalesce(c.Post,'')) as P3
,rtrim(coalesce(d.Post,'')) as P4
from
yourtable a
left outer join yourtable b on b.Name = a.Name and b.Seq = 2
left outer join yourtable c on c.Name = b.Name and c.Seq = 3
left outer join yourtable d on d.Name = c.Name and d.Seq = 4
where
a.Seq = 1
and a.Name = 'Fred'
) as T1 ;