How do I build a summary by joining to a single table with SQL Server?

I have a table which is update has some fields with repeating data. I want to roll up this data to summarize it. How do I do this for SQL Server? I have an example of the output and structure below. I've tried some different joins but I've seen repeating data and some errors that I don't understand.

Table structure

• Logfile name (string)
• Status (int) - could be 1, 2, 3 depending on app input

Data

f1, 3, 0
f1, 2, 1
f1, 3, 0
f2, 1, 1
f2, 1, 1
f2, 2, 1
....

Output

File | Count of status == 1 | Count of status == 2 
f1   | 59       开发者_开发技巧           | 43
f2   | 28                  | 99
f3   | 23                  | 16

Assuming you are using SQL Server 2005 or above, here is the code:

DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT  @cols = STUFF(( SELECT distinct  ',' + QuoteName(cast([status] as varchar))
                        FROM LogTable FOR XML PATH('') ), 1, 1, '') 


SET @sqlquery = 'SELECT * FROM
      (SELECT UserIndex,  [status]
       FROM LogTable ) base
       PIVOT (Count(status) FOR [status]
       IN (' + @cols + ')) AS finalpivot'

EXECUTE ( @sqlquery )

This will work no matter how many different status you have. It dynamically assembles a query with PIVOT.

Update

As @JonH pointed out, there was a vulnerability in the code I posted, which made possible an injection attack. This is now fixed, by using QUOTENAME when forming the column names.

Other examples:

• SQL Server PIVOT perhaps?
• Pivot data in T-SQL

Summarize data using rollup:

http://msdn.microsoft.com/en-us/library/ms189305(v=sql.90).aspx

What version of sql server are you using ?

If you dont have want to use rollup this should help:

SELECT
      FileName,
      SUM(CASE WHEN Status = 1 THEN 1 ELSE 0 END) AS CountOf1,
      SUM(CASE WHEN Status = 2 THEN 1 ELSE 0 END) AS CountOf2,
      SUM(CASE WHEN Status = 3 THEN 1 ELSE 0 END) AS CountOf3
FROM
      MyTable
GROUP BY FileName
ORDER BY FileName

SELECT 
    file,
    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS [Count of status == 1] ,
    SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) AS [Count of status == 2] 
FROM Table 
GROUP BY file 
ORDER BY file