• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 135
  • Last Modified:

Flatten a query to get a list values for a column

I TSQL I can do the following:
declare @t table(id int, name varchar(90),somecolumn varchar(90))
insert into @t
    select 1,'ABC','X' union all
    select 1,'ABC','Y' union all
    select 1,'ABC','Z' union all
    select 2,'MNO','R' union all
    select 2,'MNO','S'

select ID,Name,
    stuff((select ',' + CAST(t2.SomeColumn as varchar(1000))
     from @t t2 where t1.id = t2.id and t1.name = t2.name
     for xml path('')),1,1,'') SomeColumn
from @t t1
group by id,Name

Open in new window

with the following results:
ID      Name      SomeColumn
1      ABC      X,Y,Z
2      MNO      R,S

How would I do this with a VFP Select?
0
Peter H.
Asked:
Peter H.
1 Solution
 
Olaf DoschkeSoftware DeveloperCommented:
http://msdn.microsoft.com/en-us/library/66dhb85f(v=vs.80).aspx#fogrfconsiderationsforsqlselectstatementsanchor2

That's all aggregation functions. So you will not be able to concatenate strings within a select query. The only way to compose a string would be within a function, but I see no way of it being called three or two times respectively and returning one value each.

So you'd do that outside of the query.

If min and max values would suffice, you could do:
Create Cursor curTest (id i,name v(90),somecolumn v(90))
Insert into curTest Values (1,'ABC','X')
Insert into curTest Values (1,'ABC','Y')
Insert into curTest Values (1,'ABC','Z')
Insert into curTest Values (2,'MNO','R')
Insert into curTest Values (2,'MNO','S')

Select ID, Name, MIN(somecolumn)+'-'+MAX(somecolumn) as SomeColumn ;
from curTest ;
group by id, name

Open in new window

Bye, Olaf.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now