Concatenating multiple fields across multiple rows

Question

Assuming I have a query returning

OrderID   Date           Product    Count
  1       2015-01-01     "Bread"      1
  2       2015-02-02     "Water"      2
  2       2015-02-02     "Bread"      7
  2       2015-02-02     "Soap"       3

How can I make this query to return 2 records (group by ID) concatenating multiple columns

OrderID   Date           Detail
  1       2015-01-01     "Bread (1)"
  2       2015-02-02     "Bread (7), Soap (3), Water (2)"

Where I concatenate:

1. Product Names and their Count value showing (in brackets)
2. Order by Product Name

into a "Detail" NVarchar result ??

Answer 1

you didn't mention if date is different for same orderid then what will happen.

Try this,

Declare @orders TABLE ([OrderID] INT,[Date] DATE,[Product] VARCHAR(10),[Count] INT)

    INSERT INTO @orders
    VALUES (1,'2015-01-01','Bread',1),
            (2,'2015-02-02','Water',2),
            (2,'2015-02-02','Bread',7),
            (2,'2015-02-02','Soap',3),
                (2,'2015-03-02','Soap',3)

    ;WITH CTE
    AS (
        SELECT *
            ,ROW_NUMBER() OVER (
                PARTITION BY orderid
                ,[date] ORDER BY [date]
                ) rn
        FROM @orders
        )
    SELECT OrderID
        ,[date]
        ,stuff((
                SELECT ',' + Product + ' ( ' + CONVERT(VARCHAR,[Count]) + ' ) '
                FROM cte
                WHERE orderid = a.OrderID
                    AND [Date] = a.[Date]
                FOR XML path('')
                ), 1, 1, '') [details]
    FROM CTE A
    WHERE rn = 1

Answer 2

I'll let you handle the trailing comma removal.

CREATE TABLE orders ([OrderID] INT,[Date] DATE,[Product] VARCHAR(10),[Count] INT)

INSERT INTO orders
VALUES (1,'2015-01-01','Bread',1),
        (2,'2015-02-02','Water',2),
        (2,'2015-02-02','Bread',7),
        (2,'2015-02-02','Soap',3)

SELECT
    OrderID,
    [Date],
    (
        SELECT DISTINCT 
            [Product] + '(' + CONVERT(VARCHAR,[Count]) + '), '
        FROM 
            orders o1
        WHERE o1.OrderID = o.OrderID
        FOR XML PATH('')    
     ) Detail
FROM orders o
GROUP BY OrderID, [Date]