SQL Server 2016: how to get a single row view

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

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