Flatten a query to get a list values for a column

Posted on 2015-01-06
Medium Priority
Last Modified: 2015-01-10
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?
Question by:Peter H.
1 Comment
LVL 30

Accepted Solution

Olaf Doschke earned 2000 total points
ID: 40534476

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.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
The article describes step by step how to validate the SRA software certificate once installed on the Site Recovery Manager server. I hope it will be very useful for all administrators of virtualized environments.
If you are looking for an automated solution for backup single or multiple Office 365 user mailboxes to Outlook data file, then you can use Kernel Office 365 Backup & Restore tool. Go through the video to check out the steps to backup single or mult…
To export Lotus Notes to Outlook PST or Exchange and Domino Server files to Exchange Server or PST files with ease, go for Kernel for Lotus Notes to Outlook conversion tool. Through the video, you can watch the conversion process. A common user with…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question