I want to display output in a single row in T-SQL

Question

Above is the input table. I am trying to bring all the childsku products in a single row with its count and its associated parent sku.But it is not working as expected

         select [Parent SKU (PartNo) ],count([Child SKU]),[Child SKU] 
from tbl_name
       group by [Parent SKU (PartNo) ],[Child SKU]

Now I want the desired output to be like this:

score 2 · Answer 1 · edited Jan 18 '21 at 12:32

2

Use STUFF.

Query

SELECT t.ParentSKU, COUNT(t.ChildSKU) as Total,
STUFF((
    SELECT ',' + ChildSKU
    FROM tbl_name 
    WHERE ParentSKU = t.ParentSKU
    FOR XML PATH('')
), 1, 1, '') AS ChildSKU
FROM tbl_name t
GROUP BY ParentSKU;

Demo

edited Jan 18 '21 at 12:32

Community

1
1

answered Jun 20 '16 at 13:01

Ullas

11,450
4
33
50

its not working... https://postimg.org/image/45plo3gbr/ – sql_lover Jun 20 '16 at 13:08
@sql_lover : updated the answer. – Ullas Jun 20 '16 at 13:09

score 2 · Accepted Answer · answered Jun 20 '16 at 13:08

You can use STUFF for this;

Test Data;

IF OBJECT_ID('tempdb..#TestData') IS NOT NULL DROP TABLE #TestData
GO
CREATE TABLE #TestData (ChildSKU varchar(10), ParentSKU varchar(10))
INSERT INTO #TestData (ChildSKU, ParentSKU)
VALUES
('B001L','B001')
,('B001M','B001')
,('B001S','B001')
,('B001XL','B001')

Query;

SELECT 
a.ParentSKU
,COUNT(ChildSKU) ChildTotal
,STUFF((SELECT ', ' + ChildSKU
           FROM #TestData b 
           WHERE b.ParentSKU = a.ParentSKU 
          FOR XML PATH('')), 1, 2, '') AS ChildSKU
FROM #TestData a
GROUP BY ParentSKU

Gives the results as;

ParentSKU   ChildTotal  ChildSKU
B001        4           B001L, B001M, B001S, B001XL

Great, glad it worked for you. Please mark one of the supplied answers as accepted to show the issue is resolved :) — Rich Benner, Jun 20 '16 at 13:13

I want to display output in a single row in T-SQL

2 Answers2

Demo