How to Comma separate multiple rows obtained from a SQL Query

Question

I wrote a query as :

select tbl1.Id, tbl1.FirstName, tbl1.MiddleInit, tbl1.LastName, tbl1.SocialSecNum, tbl1.DateOfBirth, 
tbl1.EyeColor, tbl1.Sex, tbl1.AlertNotes, tbl1.RiskNotes, tbl1.Height, tbl1.[Weight], tbl1.AllergyNotes, 
tbl2.HairColor, tbl3.SexualConsent, tbl4.MaritalStatus, tbl5.Ethnicity, tbl6.Veteran, tbl7.Religion, tbl8.Race, 
tbl9.[Language] as [Language]

from

(SELECT C.Id, C.FirstName, C.MiddleInit, C.LastName, C.SocialSecNum, C.DateOfBirth, C.Sex, 
GL.LookupItem as EyeColor, CC.AlertNotes, CC.RiskNotes, CC.Height, CC.[Weight], CC.AllergyNotes  
FROM dbo.Client C INNER JOIN dbo.ClientCharacteristic CC ON C.Id = CC.ClientId INNER JOIN dbo.GeneralLookup GL ON 
GL.Id=CC.glEyeColorId) tbl1,  

(SELECT GL.LookupItem as HairColor  
FROM dbo.ClientCharacteristic CC INNER JOIN dbo.GeneralLookup GL ON 
GL.Id=CC.glHairColorId) tbl2,

(SELECT GL.LookupItem as SexualConsent  
FROM dbo.ClientCharacteristic CC INNER JOIN dbo.GeneralLookup GL ON 
GL.Id=CC.glSexConsentId) tbl3,

(SELECT GL.LookupItem as MaritalStatus  
FROM dbo.Client C INNER JOIN dbo.GeneralLookup GL ON 
GL.Id=C.glMaritalStatusId where C.Id=2) tbl4,

(SELECT GL.LookupItem as Ethnicity 
FROM dbo.GeneralLookupTransition GLT INNER JOIN dbo.GeneralLookup GL ON 
GL.Id=GLT.glValueId where GLT.ParentRecordId=2 and GLT.ControlName='CONSUMER_ETHNICITY_LIST') tbl5, 

(SELECT GL.LookupItem as Veteran  
FROM dbo.Client C INNER JOIN dbo.GeneralLookup GL ON 
GL.Id=C.glVeteranId where C.Id=2) tbl6, 

(SELECT GL.LookupItem as Religion 
FROM dbo.GeneralLookupTransition GLT INNER JOIN dbo.GeneralLookup GL ON 
GL.Id=GLT.glValueId where GLT.ParentRecordId=2 and GLT.ControlName='CONSUMER_RELIGION_DROPDOWN') tbl7, 

(SELECT GL.LookupItem as Race 
FROM dbo.GeneralLookupTransition GLT INNER JOIN dbo.GeneralLookup GL ON 
GL.Id=GLT.glValueId where GLT.ParentRecordId=2 and GLT.ControlName='CONSUMER_RACE_DROPDOWN') tbl8, 

(SELECT GL.LookupItem as [Language] 
FROM dbo.GeneralLookupTransition GLT INNER JOIN dbo.GeneralLookup GL ON 
GL.Id=GLT.glValueId where GLT.ParentRecordId=2 and GLT.ControlName='CONSUMER_CHARACTERISTIC_LANGUAGE_DROPDOWN') tbl9

The result is:

enter image description here

These some of the columns I got from this query. See the column Ethnicity. It has 3 different records against a single client. Please tell me how can I convert these three records in a single comma separated records in the same column and these 3 rows become a single row.

Please save the image and then see. May be it is not visible here!

why not return multiple tables from the query and make relations between them in asp.net? — Naveed Butt
– Naveed Butt, Commented Apr 4, 2011 at 9:39
No I can't. I just need this Ehtnicity comma-separated. This is the requirement. — asma
– asma, Commented Apr 4, 2011 at 9:43

Mikael Eriksson · Accepted Answer · 2011-04-04 09:59:30Z

Your query is a bit too much for me to process. But here is the answer to the question...

How to Comma separate multiple rows obtained from a SQL Query

You can use for xml path('') like this.

declare @T table (GroupID int, Value varchar(10))

insert into @T values (1, 'Row 1')
insert into @T values (1, 'Row 2')
insert into @T values (2, 'Row 3')
insert into @T values (2, 'Row 4')
insert into @T values (2, 'Row 5')

select GroupID,
  stuff(
  (select ','+Value as '*'
   from @T as T2
   where T2.GroupID = T1.GroupID
   for xml path('')), 1, 1, '') as [Values]
from @T as T1
group by GroupID

Result

GroupID     Values
----------- ------------------
1           Row 1,Row 2
2           Row 3,Row 4,Row 5

Good answer; there is a blog post similar to this solution at sqlservercurry.com/2011/03/… which gives more details on why this work.

Tim Schmelter · Accepted Answer · 2011-04-04 10:11:07Z

0

You could write an UDF:

CREATE FUNCTION [dbo].[getEthnicity]
(
    @id Int,
    @delimiter varchar(5)
)
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @Ethnicity VARCHAR(8000) 
    SELECT   @Ethnicity = COALESCE(@Ethnicity + @delimiter, '') + GLT.DisplayColumn     
    FROM dbo.GeneralLookupTransition GLT INNER JOIN dbo.GeneralLookup GL ON 
        GL.Id=@ID where GLT.ParentRecordId=2 and GLT.ControlName='CONSUMER_ETHNICITY_LIST')
    return @Ethnicity
END

This is only an example because i dont know your datamodel.

edited Apr 4, 2011 at 10:11

answered Apr 4, 2011 at 10:00

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Collectives™ on Stack Overflow

How to Comma separate multiple rows obtained from a SQL Query

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related