Solved

How to collapse rows returned from SQL SELECT into one?

Posted on 2004-04-06
2
444 Views
Last Modified: 2008-02-20
Hello,

the following query joins two tables and produces the following results:

SELECT DT1.Name, DT1.SectorID, DT1.ExposureBiasID, DT2.AssetClassID
FROM DT_Strategy DT1, DT_StrategyAssetClass DT2
WHERE DT1.ID = DT2.StrategyID AND DT1.ID = 1

------------------------------------------------------------------
Relative Value Equity Long      4      1      1
Relative Value Equity Long      4      1      2
Relative Value Equity Long      4      1      3
------------------------------------------------------------------

All the resulting rows are equal except for the value in the last column. I would like to collapse them into one row as follows:

----------------------------------------------------------------------
Relative Value Equity Long      4      1      1,2,3
----------------------------------------------------------------------

Is this possible in SQL? Or do I need to do it programatically?

Thanks,
Stefano
0
Comment
Question by:sdamiano
2 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
Comment Utility
On SQL 2000, you can use a user-defined function.  For example:


CREATE FUNCTION dbo.getAssetClassIDs (
      @ID INT
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @AssetClassIDs VARCHAR(1000)
SET @AssetClassIDs = ''
SELECT @AssetClassIDs = @AssetClassIDs + CAST(AssetClassID AS VARCHAR(10)) + ','
FROM DT_StrategyAssetClass
WHERE StrategyID = @ID
RETURN LEFT(@AssetClassIDs, LEN(@AssetClassIDs) - 1)
END --FUNCTION
GO


Then, to use the function:

SELECT DT1.Name, DT1.SectorID, DT1.ExposureBiasID, dbo.getAssetClassIDs(DT1.ID)
FROM DT_Strategy DT1
WHERE DT1.ID = 1
0
 

Author Comment

by:sdamiano
Comment Utility
Scott,

it works perfectly!

Thanks,
Stefano
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now