How to Comma separate multiple rows obtained from a SQL Query
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 I开发者_运维百科NNER 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:
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!
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
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.
精彩评论