Redundant data to comma separated string

Question

I wish to convert redundant row values into a comma separated string to build a JSON. Here in my example the columns I need to convert to comma separated string is attrValueId, attrValue and name.

Please use the snippet to build the schema

CREATE TABLE t
    ([attrId] int, [displayPosition] int, 
     [attrValueId] int, [attrValue] varchar(30),
    name varchar(30), attrName varchar(30),attrType varchar(30),
    isRequired bit);

INSERT INTO t    VALUES
    (1,2,1,'123',NULL,'testattribute','dropdown',0);
INSERT INTO t  VALUES
    (1,2,2,'1234',NULL,'testattribute','dropdown',0);
INSERT INTO t    VALUES
    (3,1,6,'miuu2',NULL,'mult','multi-select',1);
INSERT INTO t VALUES
    (3,1,7,'miuu3396',NULL,'mult','multi-select',1);

The table data is like

attrId  displayPosition attrValueId attrValue   name    attrName        attrType        isRequired
1       2               1           123         NULL    testattribute   dropdown        0
1       2               2           1234        NULL    testattribute   dropdown        0
3       1               6           miuu2       NULL    mult            multi-select    1
3       1               7           miuu3396    NULL    mult            multi-select    1

My required result is

  attrId    displayPosition attrValueId attrValue      name      attrName      attrType     isRequired
  1         2               1,2         1234,1234      NULL,NULL testattribute  dropdown     0
  3         1               6,7         miuu2,miuu3396 NULL,NULL mult           multi-select 1

My ultimate aim is to construct a JSON string in the format

    [
{"attrId":"1","displayPosition":"2","attrValueId":["1,2"],"attrValue":["1234,1234"],"name":["null","null"],"attrName":"testattribute","attrType":"dropdown","isRequired":"0"}
,
{second row goes here}]

You can use [this](https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver15) for converting the output to JSON — Aaron Pereira, Jul 06 '20 at 06:22

mehtasuraj09 · Answer 1 · 2020-07-07T08:01:12.717

0

Try using STRING_AGG() by doing GROUP BY over attrId & displayPosition

I have one sample for you.

SELECT 
    [attrId]
    ,[displayPosition]
    ,STRING_AGG([attrValueId],',') [attrValueId] 
    ,STRING_AGG(name,',') name 
    ,STRING_AGG([attrValue],',') [attrValue]   
    ,STRING_AGG(attrType,',') attrType, isRequired
 FROM t group by [attrId],[displayPosition],isRequired

I have assumed that [attrId],[displayPosition],isRequired uniquely represent one row.

For Old SQL Server versions, try STUFF() Check the following threads: Group By and STUFF combined result in sql server

How to use GROUP BY to concatenate strings in SQL Server?

edited Jul 07 '20 at 08:01

answered Jul 06 '20 at 06:33

mehtasuraj09

139
12

I am using version 2016 – Surensiveaya Jul 06 '20 at 06:40
In that case, please follow the following question's thread : https://stackoverflow.com/questions/54553172/string-agg-in-sql-server-2016 – mehtasuraj09 Jul 06 '20 at 06:50
Thanks, but i dont want to enable CLR. Can u give any alternate solution – Surensiveaya Jul 06 '20 at 07:02
SELECT attrId, string = STUFF( (SELECT ',' + attrValue FROM t FOR XML PATH ('')), 1, 1, '' ) FROM t GROUP BY attrId,attrName It gives wrong data – Surensiveaya Jul 06 '20 at 07:33
can u help with this? – Surensiveaya Jul 06 '20 at 10:04
follow : https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server – mehtasuraj09 Jul 07 '20 at 08:00

Redundant data to comma separated string

1 Answers1