Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

How to collapse rows returned from SQL SELECT into one?

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
sdamiano
Asked:
sdamiano
1 Solution
 
Scott PletcherSenior DBACommented:
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
 
sdamianoAuthor Commented:
Scott,

it works perfectly!

Thanks,
Stefano
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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