Solved

Flatten a query to get a list values for a column

Posted on 2015-01-06
1
111 Views
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?
0
Comment
Question by:Peter H.
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 40534476
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Activating an OLE Program 10 27
not sure how to proceed 6 431
Toolbar in a formset deletion 1 308
Best practices regarding backing up maintenance programming 20 142
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 world seems to conceive of a curious bubble separating IT from “the business.”  More so than just about any other pursuit in the commercial world, people think of IT as some kind of an island.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

726 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