SQL groupings for desired expected report

Question

The requested result is:

styleNumber colorCode   asset.url
1021A166    600 https://IMAGE , https://IMAGE 1, https://IMAGE 2,https://IMAGE 3,https://IMAGE 4
1021A163    1   https://IMAGE , https://IMAGE 1, https://IMAGE 2,https://IMAGE 3,

And I do have database data that is:

styleNumber colorID assetURL
1021A163    1   https://images
1021A163    1   https://images1
1021A163    1   https://images2
1021A166    600 https://images
1021A166    600 https://images1
1021A166    600 https://images2
1021A166    600 https://images3
1021A166    600 https://images4

May you help me on how to query to get the desired result.

You want some kind of string aggregation. – shawnt00 Apr 06 '21 at 04:20 — shawnt00, Apr 06 '21 at 04:20

score 0 · Answer 1 · answered Apr 06 '21 at 04:41

0

If this is what you searching for:

Your database:

styleNumber	colorID	assetURL
1021A163	1	https://images
1021A163	1	https://images1
1021A163	1	https://images2
1021A163	1	https://images3

Your expected result:

styleNumber	colorID	assetURL
1021A163	1	https://images, https://images1, https://images2, https://images3

Then here is the query:

SELECT styleNumber, colorId, GROUP_CONCAT(assetURL)
FROM yourtable
group by styleNumber, colorId

answered Apr 06 '21 at 04:41

Nguyễn Văn Quyền

350
1
10

How did you know the OP’s database suppprts GROUP_CONCAT? – Caius Jard Apr 06 '21 at 04:53
Hi Nguyen Van, Please see below Error Message Msg 195, Level 15, State 10, Line 1 'GROUP_CONCAT' is not a recognized built-in function name. – Mervin Contawe Apr 06 '21 at 04:58
@MervinContawe which sql server are you using? – Nguyễn Văn Quyền Apr 06 '21 at 05:16
Hi Nguyen, Thank you for your help, i just got the script i need. . . SELECT DISTINCT m.styleNumber , m.colorId , assetURLs = STUFF(( SELECT ', ' + md.assetURL FROM ASICS_Assets md WHERE m.styleNumber = md.styleNumber and m.colorId = md.colorID FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') FROM myTable m ORDER BY 1,2 – Mervin Contawe Apr 06 '21 at 05:36

score 0 · Answer 2 · answered Apr 06 '21 at 12:15

Since SQL Server 2017, you can use string_agg():

select styleNumber, colorCode,
       string_agg(asset_URL, ', ') within group (order by asset_URL)
from t
group by styleNumber, colorCode;

In earlier versions you need to use a workaround such as for xml path.

SQL groupings for desired expected report

2 Answers2