Use COALESCE with a group of

Question

I have one table that contains the following data

pid category name subcategoryname -------------------------------------------------------- 424 Bathing & Infant Care Baby Lotion 428 Bathing & Infant Care Baby Lotion 445 Bathing & Infant Care Bath Accessories 337 Bathing & Infant Care Bath Accessories 69 Bathing & Infant Care Bath Accessories 143 Bathing & Infant Care Bath Accessories 147 Bathing & Infant Care Bath Accessories 213 Bathing & Infant Care Bath Accessories 270 Bathing & Infant Care Bath Accessories 197 Bathing & Infant Care Bath Toys 390 Bathing & Infant Care Bath Toys 396 Bathing & Infant Care Bath Toys 416 Bathing & Infant Care Bath Toys 361 Bathing & Infant Care Bath Tub 447 Bathing & Infant Care Bath Tub 464 Bathing & Infant Care Bath Tub 491 Bathing & Infant Care Bath Tub 204 Bathing & Infant Care Bath Tub 286 Bathing & Infant Care Bath Tub 237 Bathing & Infant Care Bath Tub 240 Bathing & Infant Care Bath Tub 248 Bathing & Infant Care Bath Tub 148 Bathing & Infant Care Bath Tub 23 Bathing & Infant Care Bath Tub 153 Bathing & Infant Care Humidifier 485 Bathing & Infant Care Humidifier 14 Bathing & Infant Care Other 64 Bathing & Infant Care Other 73 Bathing & Infant Care Shampoo & Body Wash 449 Bathing & Infant Care Towels 

Desired result: I need to write a sql script, where I pass category name and output should be

 subcategoryname | total count of records | comma separated list of pid 

therefore, if I pass “Bathing and Baby Care” to my SQL, it should give me

 subcategoryname totalcount pid -------------------------------- Baby Lotion 2 424,428 Other 2 14,64 Baby Accesso 7 445,337,69,143,147,213,270 

Answer 1

You can use FOR XML PATH and STUFF to get the result:

 select subcategoryname, count(*) total, STUFF( (SELECT ', ' + cast(t2.pid as varchar(50)) FROM yt t2 where t.categoryname = t2.categoryname and t.subcategoryname = t2.subcategoryname FOR XML PATH ('')) , 1, 1, '') AS pid from yt t group by categoryname, subcategoryname; 

See SQL Fiddle with Demo . It gives the result:

 | SUBCATEGORYNAME | TOTAL | PID | --------------------------------------------------------------------------------------- | Baby Lotion | 2 | 424, 428 | | Bath Accessories | 7 | 445, 337, 69, 143, 147, 213, 270 | | Bath Toys | 4 | 197, 390, 396, 416 | | Bath Tub | 11 | 361, 447, 464, 491, 204, 286, 237, 240, 248, 148, 23 | | Humidifier | 2 | 153, 485 | | Other | 2 | 14, 64 | | Shampoo & Body Wash | 1 | 73 | | Towels | 1 | 449 |