SQL Server 2016: how to get a single row view

Question

I have been trying to word this correctly, but here's my dbfiddle

Table:

    Customerkey int NOT NULL PRIMARY KEY,
    processdate date NULL,
    CCcount int NULL,
    CHKcount int NULL,
    SACount int NULL
);

INSERT INTO products 
(Customerkey, processdate, CCcount, CHKCount, SACount) 
VALUES
(101,'20210501', 12,3,5),
(102,'20210203', 1,3,1),
(103,'20190412', 4,0,2)

SELECT  CustomerKey,  processdate,
ProductMix=STUFF  
(  
     (  
       SELECT distinct ',' + str(CCcount)  + ',' + str(CHKCount)   +  ',' + str(SACount)
       FROM products t2 
       WHERE t2.CustomerKey = t1.CustomerKey   
       and t2.processdate = t1.processdate
        ),1,1,''  
)  
FROM products t1  
GROUP BY CustomerKey,  processdate

SELECT CustomerKey, processdate,
concat(
Case when CCcount >' ' then 'CCcount' 
when CHKCount > '' then 'CHKCount'
when SACount > '' then 'SACount'
end, '') as Product_Mix_Expanded
from products

Expected Output:

CustomerKey	processdate	Product_Mix	Product_Mix_Expanded
101	2021-05-01	12,3,5	CCcount, CHKCount, SACount
102	2021-02-03	1,3,1	CCcount, CHKCount, SACount
103	2019-04-12	4,0,2	CCount, SACount

As you can see, I used STUFF, but not sure if that's the right approach. I need the Product Mix showing the count and Product Mix Expanded in the worded format. Feel free to ask questions you need more inputs. Thank you in advance.

Answer 1

Based on your sample data and expected results does the following work for you?

select CustomerKey, ProcessDate, Concat(CCcount,',',CHKcount,',',SACount) Product_Mix, 
    Concat(case when CCcount>0 then 'CCcount' else '' end,', ',
    case when CHKcount>0 then 'CHKcount' else '' end, ', ',
    case when SACount>0 then 'SACount' else '' end) Product_Mix_Expanded
from products