0

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
Jim
  • 11
  • 1
  • Why does your data have 3 columns, but you only tell us about two (logfile and status)? – Amy B Jul 27 '11 at 19:00
  • @David B - because it is a count of each distinct status type. So if he had 'n' statuses the count of columns would be n+1 (the +1 refers to the file name). Also known as a matrix. – JonH Jul 27 '11 at 19:10
  • -1 for dynamically generated database design. – Amy B Jul 27 '11 at 20:48
  • 1
    Omitting the description for the 3rd column doesn't deserve a -1. The question is clear. @JonH, David B was talking about the data, while your comment was about the output. – sayap Jul 28 '11 at 10:01

3 Answers3

2

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:

Community
  • 1
  • 1
Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
  • Give it a try. Reporting was never the same since I learned this trick. – Adriano Carneiro Jul 27 '11 at 19:28
  • isn't it annoying when the 'best' script is chosen after 4 minutes ? Nice dynamic script. +1. Here is a way to improve it slightly SELECT @cols = coalesce(@cols + ',', '') + '[' + cast([status] as varchar) + ']' FROM (select distinct status from @logtable) a order by status – t-clausen.dk Jul 27 '11 at 20:00
  • @Adrian - One thing I see about this is it is vulnerable to an injection attack. Don't concatenate your square brackets around the column name to avoid this. You could use QUOTENAME to return a unicode string. http://msdn.microsoft.com/en-us/library/ms176114.aspx – JonH Jul 28 '11 at 12:01
  • Thanks for the heads up @JonH. I just updated the code to make it more injection-proof. – Adriano Carneiro Jul 28 '11 at 13:27
0

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
JonH
  • 32,732
  • 12
  • 87
  • 145
0
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
EricZ
  • 6,065
  • 1
  • 30
  • 30