[Solved] Duplicates of multiple values selected only by one Name: comma separated

by


Create your table taro:

SELECT * INTO taro
FROM 
(
    SELECT 1111 AS [C no.], 'ken' AS [Name], 'shiro' AS Item, '3/3/2000 12:22' AS [Date], '$25' AS Amount
    UNION ALL
    SELECT 1111 AS [C no.], 'ken' AS Name, 'aeshte' AS Item, '3/3/2000 12:22' AS [Date], '$25' AS Amount
    UNION ALL 
    SELECT 1111 AS [C no.], 'ken' AS Name, 'taro' AS Item, '3/3/2000 12:22' AS [Date], '$25' AS Amount
    UNION ALL
    SELECT 2222,'yakumo','tesen','3/4/2000 12:22','$22'
    UNION ALL
    SELECT 2222,'yakumo','aoie','3/4/2000 12:22','$22'
    UNION ALL
    SELECT 3333,'kage','manase','3/5/2000 12:22','$21'
) A

Group it by your columns and put items together separated by commas

SELECT
    [C No.],
    Name,
    [Date],
    Amount,
    STUFF((
    SELECT ',' + A.Item
    FROM taro A
    WHERE   A.[C no.] = B.[C no.]
    AND     A.Name = B.Name
    AND     A.[Date] = B.[Date]
    AND     A.[Amount] = B.[Amount]
    ORDER BY item
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,1,'') AS ItemValue
FROM taro B
GROUP BY [C No.],Name,[Date],Amount

Results:

C No.       Name   Date           Amount ItemValue
----------- ------ -------------- ------ --------------------
1111        ken    3/3/2000 12:22 $25    aeshte,shiro,taro
2222        yakumo 3/4/2000 12:22 $22    aoie,tesen
3333        kage   3/5/2000 12:22 $21    manase

4

solved Duplicates of multiple values selected only by one Name: comma separated