CODEWITHSUNDEEP
sqlsql-server
Amit Kumar
Amit Kumar

Reputation: 5962

Select comma sperated column value

I'm trying to get comma , separated column value in SQL Server. 

Select 
CT.ClaimTypeId, CT.ClaimTypeName,CT.MHWSchemeNo,CT.MHWClaimTypeID,CFCTR.FormId,CF.Name,
FF.FormId,FF.FieldId,FF.Label,FF.Name,FF.IsDefaultField,FF.IsFieldLabelVisible,FF.IsRequired,FF.Type,

Label = STUFF((
          SELECT ',' +JFFO.Label
          FROM dbo.FormFieldOption JFFO
          where JFFO.FieldId = FF.FieldId
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
Value= STUFF((
          SELECT ',' +JFFO.Value
          FROM dbo.FormFieldOption JFFO
          where JFFO.FieldId = FF.FieldId
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
AdditionalTextfieldName= STUFF((
          SELECT ',' +JFFO.AdditionalTextfieldName
          FROM dbo.FormFieldOption JFFO
          where JFFO.FieldId = FF.FieldId
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

from EmployeeClaimTypeRelationship ECR 
LEFT JOIN ClaimType CT on CT.MHWSchemeNo=ECR.MHWSchemeNo
LEFT JOIN ClaimFormClaimTypeRelationship CFCTR on CFCTR.ClaimTypeId=CT.ClaimTypeId
LEFT JOIN ClaimForm CF on CF.FormId=CFCTR.FormId
LEFT JOIN FormField FF on FF.FormId=CFCTR.FormId
LEFT JOIN FormFieldOption FFO on FFO.FieldId=FF.FieldId
where ECR.MHWUserId='CITITEST1'
group by FF.FieldId,CT.ClaimTypeId, CT.ClaimTypeName,CT.MHWSchemeNo,CT.MHWClaimTypeID,CFCTR.FormId,CF.Name,
FF.FormId,FF.FieldId,FF.Label,FF.Name,FF.IsDefaultField,FF.IsFieldLabelVisible,FF.IsRequired,FF.Type

This is my query. And it is giving me the correct result. Here I'm using group by clause.

Now I want to optimize this query.

As you can see above, I have 3 columns which have comma , separated values which are coming from same table.

Is there any way so that I don't have to write multiple time same query for different fields?

Like this

Select 
CT.ClaimTypeId, CT.ClaimTypeName,CT.MHWSchemeNo,CT.MHWClaimTypeID,CFCTR.FormId,CF.Name,
FF.FormId,FF.FieldId,FF.Label,FF.Name,FF.IsDefaultField,FF.IsFieldLabelVisible,FF.IsRequired,FF.Type,

Label = STUFF((
          SELECT ',' +JFFO.Label
          FROM dbo.FormFieldOption JFFO
          where JFFO.FieldId = FF.FieldId
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
Value,AdditionalTextfieldName = STUFF((
          SELECT ',' +JFFO.Value as Value,','+JFFO.AdditionalTextfieldName as AdditionalTextfieldName
          FROM dbo.FormFieldOption JFFO
          where JFFO.FieldId = FF.FieldId
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

from EmployeeClaimTypeRelationship ECR 
LEFT JOIN ClaimType CT on CT.MHWSchemeNo=ECR.MHWSchemeNo
LEFT JOIN ClaimFormClaimTypeRelationship CFCTR on CFCTR.ClaimTypeId=CT.ClaimTypeId
LEFT JOIN ClaimForm CF on CF.FormId=CFCTR.FormId
LEFT JOIN FormField FF on FF.FormId=CFCTR.FormId
LEFT JOIN FormFieldOption FFO on FFO.FieldId=FF.FieldId
where ECR.MHWUserId='CITITEST1'
group by FF.FieldId,CT.ClaimTypeId, CT.ClaimTypeName,CT.MHWSchemeNo,CT.MHWClaimTypeID,CFCTR.FormId,CF.Name,
FF.FormId,FF.FieldId,FF.Label,FF.Name,FF.IsDefaultField,FF.IsFieldLabelVisible,FF.IsRequired,FF.Type

Upvotes: 0

Views: 72

Answers (3)

M.Ali
M.Ali

Reputation: 69524

I would create a UDF Table-Valued Function and call that, it will also give you some performance benefit

Function Definition

CREATE FUNCTION dbo.fn_FormFieldOptionList ( @FieldId INT)
RETURNS TABLE
AS
RETURN (
SELECT DISTINCT 
     Label = STUFF((
              SELECT ',' +JFFO.Label
              FROM dbo.FormFieldOption JFFO
              where JFFO.FieldId = FF.FieldId
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    ,Value= STUFF((
              SELECT ',' +JFFO.Value
              FROM dbo.FormFieldOption JFFO
              where JFFO.FieldId = FF.FieldId
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    ,AdditionalTextfieldName= STUFF((
              SELECT ',' +JFFO.AdditionalTextfieldName
              FROM dbo.FormFieldOption JFFO
              where JFFO.FieldId = FF.FieldId
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

FROM dbo.FormFieldOption FF
WHERE FF.FieldId = @FieldId )

Your Query

Select 
CT.ClaimTypeId, CT.ClaimTypeName,CT.MHWSchemeNo
,CT.MHWClaimTypeID,CFCTR.FormId,CF.Name
,FF.FormId,FF.FieldId,FF.Label,FF.Name
,FF.IsDefaultField,FF.IsFieldLabelVisible
,FF.IsRequired,FF.Type,f.Label. f.Value ,f.AdditionalTextfieldName 

from EmployeeClaimTypeRelationship ECR 
LEFT JOIN ClaimType CT on CT.MHWSchemeNo=ECR.MHWSchemeNo
LEFT JOIN ClaimFormClaimTypeRelationship CFCTR on CFCTR.ClaimTypeId=CT.ClaimTypeId
LEFT JOIN ClaimForm CF on CF.FormId=CFCTR.FormId
LEFT JOIN FormField FF on FF.FormId=CFCTR.FormId
OUTER APPLY  dbo.fn_FormFieldOptionList(FF.FieldId) f
where ECR.MHWUserId='CITITEST1'

Upvotes: 1

Midhun m k
Midhun m k

Reputation: 66

Here in this query, we are joining dbo.FormFieldOption twice. Good optimization & speed can be achieved if we can restructure the query. Try to restructure the join towards table dbo.FormFieldOption to single.

Upvotes: 0

Aconcagua
Aconcagua

Reputation: 25526

You could have a view.

CREATE VIEW view_name AS
SELECT * FROM ... WHERE ...

(Instead of *, you might prefer to select the union of all columns you ever might need and possibly give them aliases.) Then you would only need to

SELECT column1, column2, column3
FROM view_name
WHERE [additional conditions]

Upvotes: 1

Related Questions