Solved

How to collapse rows returned from SQL SELECT into one?

Posted on 2004-04-06
2
447 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
ID: 10770277
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
ID: 10770600
Scott,

it works perfectly!

Thanks,
Stefano
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

919 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

15 Experts available now in Live!

Get 1:1 Help Now