Q: 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


A: 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

A: 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


A: 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 

