Improve company productivity with a Business Account.Sign Up

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

Concantenate over multiple rows: SQL 2005

Consider this table if you will

X1, X2, Y, Details
US, IT, Catagory1, Manager
US, IT, Catagory1, Sales
US, IT, Catagory2, Programmer

I want to concatenate the details of rows with the same X1, X2 and Y
So the result should be

US, IT, Catagory1, Manager + Sales
US, IT, Catagory2, Programmer

What would the SQL or stored procedure look like to pull this off optmized for SQLserver 2005?
0
DaFou
Asked:
DaFou
  • 2
  • 2
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi DaFou,

create a function like this

CREATE function dbo.RetCSVs (
@x1 varchar(100),
@x2 varchar(100),
@Y  varchar(100)
)
Returns Varchar(8000)
AS
BEGIN
    declare @out Varchar(8000)
    SELECT @out = COALESCE (RTRIM(@out)+',','')+Details
    FROM urTable ------------ replace  this with ur table name
    WHERE X1 = @x1 AND  X2 = @x2 AND Y =@y

    return @out
END
GO

And call like

SELECT X1,X2,Y, dbo.RetCSVs(X1,X2,Y) As DesiredColumnName
FROM urTable



Aneesh R!
0
 
DireOrbAntCommented:
You might want to make the last call:
SELECT X1,X2,Y, dbo.RetCSVs(X1,X2,Y) As DesiredColumnName
FROM urTable
GROUP BY X1,X2,Y
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Thanks DireOrbAnt

OR ELse

SELECT DISTINCT X1,X2,Y, dbo.RetCSVs(X1,X2,Y) As DesiredColumnName
FROM urTable
0
 
DireOrbAntCommented:
>>OR ELse SELECT DISTINCT
I would not do that, because SQL doesn't know that dbo.RetCSVs(X1,X2,Y) will return the same value for each rows. The DISTINCT will filter AFTER running all those calls, the group by will allow the RS to be filtered before.
0
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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