How to create a resume by joining the same table with SQL Server?

I have a table in which there is an update, there are some fields with duplicate data. I want to generalize this data in order to generalize it. How to do this for SQL Server? I will give an example of output and structure below. I tried several different joins, but I saw duplicate data and some errors that I don't understand.

Table structure

  • Log file name (string)
  • Status (int) - may be 1, 2, 3 depending on the application input

Data

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

Exit

 File | Count of status == 1 | Count of status == 2 f1 | 59 | 43 f2 | 28 | 99 f3 | 23 | 16 
0
source share
3 answers

Summarize data using a drive:

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

What version of sql server are you using?

If you do not 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 
0
source

Assuming you are using SQL Server 2005 or higher, 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 much status you have. It dynamically collects the request using PIVOT .

Update

As @JonH pointed out, the code I posted had a vulnerability that made an injection attack possible. This has now been fixed with QUOTENAME when generating column names.

Other examples:

+2
source
 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 
0
source

Source: https://habr.com/ru/post/895804/


All Articles