SQL row-to-column (two-dimensional) Summary

I recently wrote a Sales Report SQL statement to summarize this type of two-dimensional SQL statement.

Table Structure Test

Productname salemonth salenumber

A M1 1

B M1 1

A m2 2

B m2 3

Query results are required:

Productname M1 m2

A 1 2

B 1 3

Method 1:

Create a temporary table and add data to the temporary table through a cursor.

However, this method is only suitable for column fixation. If the column is not sure, it is difficult to write. Therefore, this method is ignored.

Method 2:

Idea: Use Case to create columns in sequence.

(Case salemonth when 'm1 'then salenumber else 0 end) [M1],

(Case salemonth when 'm2' then salenumber else 0 end) [m2]

The problem is: how to obtain an uncertain column name.

declare @sql varchar(8000)

SET @sql='SELECT ProductName' 

-- In this step, the key needs to be initialized, otherwise it will be invalid. Because the varchar type is added, if any content is empty, the added content is also empty.

Select @ SQL = @ SQL + ', (Case salemonth when ''' + salemonth

+ ''' THEN SaleNumber ELSE 0 END) [' 

+ SaleMonth + ']' FROM (SELECT DISTINCT SaleMonth FROM Test) A

SET @sql = @sql + ' FROM Test'

PRINT @sql

EXEC(@sql)

The running result is as follows:

P m 1 M2 M3 M4

A 1 0 0 0
A 0 2 0 0
A 0 0 3 0
A 0 0 0 4
B 1 0 0 0
B 0 2 0 0
B 0 0 3 0
B 0 0 0 4

This is not as expected. After analysis, Max can be used to avoid case processing.

As follows:

declare @sql varchar(8000)

SET @sql='SELECT ProductName' 

-- In this step, the key needs to be initialized, otherwise it will be invalid. Because the varchar type is added, if any content is empty, the added content is also empty.

Select @ SQL = @ SQL + ', max (Case salemonth when ''' + salemonth

+ ''' THEN SaleNumber ELSE 0 END) [' + SaleMonth + ']' FROM 

(SELECT DISTINCT SaleMonth FROM Test) A

SET @sql = @sql + ' FROM Test GROUP BY ProductName'

PRINT @sql

EXEC(@sql)

The running result is as follows:

P M1 M2 M3 M4

A 1 2 3 4
B 1 2 3 4

The effect is as expected.

However, there is another problem. If there is a negative value in salenumber, a data error will occur (the data in this column will be replaced by 0 ). In this case, you need to make changes based on the actual situation.

Method 3:

The functions in sql2005 support row-to-column conversion.

Refer to: http://technet.microsoft.com/zh-cn/library/ms177410.aspx

The SQL statement is as follows:

SELECT * FROM (SELECT ProductName, SaleNumber, SaleMonth FROM Test) A 

PIVOT(MAX(SaleNumber) FOR SaleMonth IN(m1,m2,m3,m4)) B

The problem is: how to dynamically generate M1, M2...

declare @sql varchar(8000)

SELECT @sql = ISNULL(@sql + ',', '') + SaleMonth FROM Test GROUP BY SaleMonth

SET @sql = 'SELECT * FROM (SELECT ProductName, SaleNumber, SaleMonth FROM Test) A 

PIVOT(MAX(SaleNumber) FOR SaleMonth IN(' + @sql + ')) B'
PRINT @sql

EXEC(@sql)

Summary: If your table has a primary key or a column has a unique value, remember to exclude this column when creating the source.