Help writing SQL query

Possible Duplicate:
Need help with a SQL query that combines adjacent rows into a single row

So this is how my table looks.

  • SQL Server - transactions roll back on error?
  • SQL How to convert this to a SubQuery?
  • Combine results to count totals for individuals
  • How to remove special characters from a string completely in sql server
  • T-SQL: Looping through an array of known values
  • SQL Server Profiler?
  • alt text

    ..and I need to write a query to get the output like this:

    alt text

    This is not a homework question.

  • String or binary data would be truncated
  • SQL - How to identify permutations of multiple key values
  • select related to max field
  • How to sum count value in SQL Server?
  • SQL Server Convert integer to binary string
  • Maintaining subclass integrity in a relational database
  • 3 Solutions collect form web for “Help writing SQL query”

    The following answer is only valid for SQL Server 2005+:

      SELECT t.category,
             STUFF((SELECT ','+ x.prod
                      FROM TABLE x
                     WHERE x.category = t.category
                  GROUP BY x.prod
                   FOR XML PATH('')), 1, 1, '') AS prod,
             SUM(t.price) AS amt
        FROM TABLE t
    GROUP BY t.category
    

    Some sample data to test with:

    DECLARE @Sample TABLE (ID INT, Name VARCHAR(15), Price INT)
    
    INSERT  @Sample
    SELECT  1, 'Ford', 100 UNION ALL
    SELECT  1, 'Cereal', 200 UNION ALL
    SELECT  2, 'Fruits', 30 UNION ALL
    SELECT  2, 'Fruits2',70 UNION ALL
    SELECT  3, 'Soap', 40
    
    SELECT * FROM @Sample
    

    And the query would be:

    SELECT s3.ID, Stf.Conc, 
    SUM(s3.Price) PriceSum
    FROM @Sample s3 INNER JOIN (
    SELECT DISTINCT s1.ID, 
           STUFF((SELECT ',' + s2.Name 
               FROM @Sample AS s2 
               WHERE s2.ID = s1.ID 
               FOR XML PATH('')), 1, 1, '') Conc 
           FROM @Sample s1) AS Stf
    ON s3.ID = Stf.ID
    GROUP BY s3.ID, Stf.Conc
    

    This question is asked many times…
    The answer is always the same. Something like

    SELECT Category, GROUP_CONCAT(PROD SEPARATOR ", ") AS Prod, SUM(Price) AS Amt FROM yourtable GROUP BY Category
    

    should be ok.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.