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.

  • Display DataType and Size of Column from SQL Server Query Results at Runtime
  • Determining Nvarchar length
  • In SQL Server 2005 emulating autonomous transaction
  • Best way to get identity of inserted row?
  • best way to migrate a windows forms application from sql server to oracle
  • Expand header row into multiple child rows
  • alt text

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

    alt text

    This is not a homework question.

  • T-SQL Group by with a where clause
  • SQL Server query - Selecting COUNT(*) with DISTINCT
  • data in a single record
  • How do I sort a SQL Server 2008 pivot result columns and rows?
  • Logging into table in SQL Server trigger
  • T-SQL Skip Take Stored Procedure
  • 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.