How to combine values in SQL stored procedure

Question

I have a stored procedure

SELECT P.Name,P.Description,
      PP.Attribute,PD.Value
FROM admin.Profiles P
LEFT JOIN admin.ProfilePreferenceMap PPM on P.ProfileID=PPM.ProfileID
LEFT JOIN admin.ProfilePrefDtl PD on PD.ProfilePrefDtlID=PPM.PreferenceID
LEFT JOIN admin.ProfilePref PP on PP.ProfilePrefID=PD.ProfilePrefID
WHERE P.Name='Profile1'

which returns the rows like

Name          Description           Attribute  Value
Profile1    Profile 1 description     E         EV
Profile1    Profile 1 description     S         SV

I would like to get the result as

Name            Description         Attribute   Value
Profile1    Profile 1 description   E,S         EV,SV

My purpose is to convert this values to JSON data like -

[{"Name":"Profile1","Description":"Profile 1 description","Attribute":["E", "S"],"Value":["EV", "SV"]}]

score 2 · Accepted Answer · edited May 23 '17 at 11:50

You should emulate GROUP_CONCAT() which exists in MySql

SQLFiddle demo

with t as
(
  SELECT P.Name,P.Description,
      PP.Attribute,PD.Value
  FROM admin.Profiles P
  LEFT JOIN admin.ProfilePreferenceMap PPM on P.ProfileID=PPM.ProfileID
  LEFT JOIN admin.ProfilePrefDtl PD on PD.ProfilePrefDtlID=PPM.PreferenceID
  LEFT JOIN admin.ProfilePref PP on PP.ProfilePrefID=PD.ProfilePrefID
  WHERE P.Name='Profile1'
)

select 
    Name,Description, 
    stuff((select ', ' + Attribute 
           from t t2 where t1.NAme = t2.NAme and t1.Description = t2.Description
           for xml path('')),
          1,2,'') [Attributes],
    stuff((select ', ' + Value
           from t t2 where t1.NAme = t2.NAme and t1.Description = t2.Description
           for xml path('')),
          1,2,'') [Values]
from t t1
group by Name,Description

**[Similar to](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server/273330#273330)** — Prahalad Gaggar, Mar 12 '13 at 06:57

score 0 · Answer 2 · answered Mar 12 '13 at 05:10

You could use group by to group by unique name or description column but i'm not sure there is a easy way to get the comma separated value, made of values across separate rows, unless you did something with Temp tables and CONCAT() possibly?

If grouping is ok try this:

SELECT P.Name,P.Description,
      PP.Attribute,PD.Value
FROM admin.Profiles P
LEFT JOIN admin.ProfilePreferenceMap PPM on P.ProfileID=PPM.ProfileID
LEFT JOIN admin.ProfilePrefDtl PD on PD.ProfilePrefDtlID=PPM.PreferenceID
LEFT JOIN admin.ProfilePref PP on PP.ProfilePrefID=PD.ProfilePrefID
WHERE P.Name='Profile1'
GROUP BY P.Name

However, grouping like this will only show the first row (based on sort) of your Attribute and Value columns howe

it will throw error .. `column attribute, value not in group by or aggregate function` — Mudassir Hasan, Mar 12 '13 at 05:13
'group by' is not the way I am looking for, because actually I only listed out 4 fields, in real they are more than 15. Its not possible to give aggregate function for each! — Sandy, Mar 12 '13 at 05:20

How to combine values in SQL stored procedure

2 Answers2