Use COALESCE with Group by

Question

I have a single table which contains data like the following

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

The desired result is 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

so if i pass "Bathing & Infant 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 should be able to 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. Gives a 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 |