SQL Server dynamic row-to-column Conversion

1. Contents)

Contents)

Contexts)

Implementation Code (SQL Codes)

Method 1: concatenate SQL statements and static column fields;

Method 2: Use concatenated SQL statements and Dynamic Column fields;

Method 3: Use the ignore relational operator and static column fields;

Method 4: Use the dynamic relational operator and Dynamic Column fields;

Ii. Background (Contexts)

In fact, row-to-column conversion is not a new topic. It has even been broken down. Many online examples have some problems, so I hope that you can quickly see the execution results, therefore, on the basis of dynamic columns, the values required for fixed table, group field, row-to-column field, and value columns are converted into real parameterization, you only need to set the parameter value based on your environment to see the effect immediately (you can directly jump to: "parameterized dynamic PIVOT "to view the specific script code ). Column 1:

(Figure 1: Row-to-column conversion)

Iii. Implementation Code (SQL Codes)

(1) first, create a test table and insert test data into it. Table record 2 is returned:

-- Create a test table

If exists (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (n' [dbo]. [TestRows2Columns] ') AND type in (n'u '))

Drop table [dbo]. [TestRows2Columns]

GO

Create table [dbo]. [TestRows2Columns] (

[Id] [int] IDENTITY (1, 1) not null,

[UserName] [nvarchar] (50) NULL,

[Subject] [nvarchar] (50) NULL,

[Source] [numeric] (18, 0) NULL

) ON [PRIMARY]

GO

-- Insert Test Data

Insert into [TestRows2Columns] ([UserName], [Subject], [Source])

Select n 'zhang san', N 'China', 60 UNION ALL

Select n 'Li si', N 'mat', 70 UNION ALL

Select n 'wang wu', N 'English ', 80 UNION ALL

Select n 'wang wu', N 'mat', 75 UNION ALL

Select n 'wang 5', N 'China', 57 UNION ALL

Select n 'Li si', N 'China', 80 UNION ALL

Select n 'zhang san', N 'inc', 100

GO

SELECT * FROM [TestRows2Columns]

(Figure 2: Sample Data)

(2) perform row-to-column conversion in a static manner, as shown in Figure 3:

-- 1: static join row to Column

SELECT [UserName],

SUM (CASE [Subject] WHEN 'mate' THEN [Source] ELSE 0 END) AS '[math]',

SUM (CASE [Subject] WHEN 'then [Source] ELSE 0 END) AS '[English]',

SUM (CASE [Subject] WHEN 'China' THEN [Source] ELSE 0 END) AS '[language]'

FROM [TestRows2Columns]

Group by [UserName]

GO

(Figure 3: Sample Data)

(3) Implement row-to-column conversion in a dynamic way. This is achieved by concatenating SQL statements. Therefore, it applies to database Versions later than SQL Server 2000, as shown in result 2 returned by executing the script;

-- 2: dynamically concatenate rows and columns

DECLARE @ SQL VARCHAR (8000)

SET @ SQL = 'select [UserName],'

SELECT @ SQL = @ SQL + 'sum (CASE [Subject] WHEN ''' + [Subject] + ''' THEN [Source] ELSE 0 END) AS ''' + QUOTENAME ([Subject]) + ''','

FROM (select distinct [Subject] FROM [TestRows2Columns]) AS

SELECT @ SQL = LEFT (@ SQL, LEN (@ SQL)-1) + 'FROM [TestRows2Columns] GROUP BY [UserName]'

PRINT (@ SQL)

EXEC (@ SQL)

GO

(4) After SQL Server 2005, there is a special operator for converting rows and columns between the unexpect and unexpect relations. The following is a static method implementation, and the implementation result is 4:

-- 3: static sequence row-to-column Conversion

SELECT *

FROM (SELECT [UserName],

[Subject],

[Source]

FROM [TestRows2Columns]

) P Branch

(SUM ([Source]) FOR [Subject] IN ([mathematics], [English], [Chinese]) AS pvt

Order by pvt. [UserName];

GO

(Figure 4)

(5) modify the static SQL statement above, so that you do not have to worry about what is stored in the record and what column names need to be converted. The script is as follows. effect 4 is shown:

-- 4: Dynamic shard row-to-column Conversion

DECLARE @ SQL _str VARCHAR (8000)

DECLARE @ SQL _col VARCHAR (8000)

SELECT @ SQL _col = ISNULL (@ SQL _col + ',', '') + QUOTENAME ([Subject]) FROM [TestRows2Columns] GROUP BY [Subject]

SET @ SQL _str ='

SELECT * FROM (

SELECT [UserName], [Subject], [Source] FROM [TestRows2Columns]) p comment

(SUM ([Source]) FOR [Subject] IN ('+ @ SQL _col +') AS pvt

Order by pvt. [UserName]'

PRINT (@ SQL _str)

EXEC (@ SQL _str)

(6) it may be enough for many people to take the above step, but you will find that when someone else gets your code, the table name, group column, row-to-column field, and field value parameters in his environment need to be constantly modified, as shown in logic 5. Therefore, I continue to modify the above script, you only need to set your own parameters to implement row-to-column conversion, as shown in Figure 4:

-- 5: parameterized dynamic shard row-to-column Conversion

-- ===================================================== ======

-- Author: <listen to the wind and rain>

-- Create date: <2014.05.26>

-- Description: <parameterized dynamic sequence row-to-column conversion>

-- Blog:

-- ===================================================== ======

DECLARE @ SQL _str NVARCHAR (MAX)

DECLARE @ SQL _col NVARCHAR (MAX)

DECLARE @ tableName SYSNAME -- List of rows

DECLARE @ groupColumn SYSNAME -- group field

DECLARE @ row2column SYSNAME -- field of the row-modified Column

DECLARE @ row2columnValue SYSNAME -- the field for changing the column value of a row

SET @ tableName = 'testrows2columns'

SET @ groupColumn = 'username'

SET @ row2column = 'subobject'

SET @ row2columnValue = 'source'

-- Obtain possible columns from row data

SET @ SQL _str = N'

SELECT @ SQL _col_out = ISNULL (@ SQL _col_out + '','', '''') + QUOTENAME (['+ @ row2column +'])

FROM ['+ @ tableName +'] group by ['+ @ row2column +']'

-- PRINT @ SQL _str

EXEC sp_executesql @ SQL _str, n' @ SQL _col_out NVARCHAR (MAX) output', @ SQL _col_out = @ SQL _col OUTPUT

-- PRINT @ SQL _col

SET @ SQL _str = N'

SELECT * FROM (

SELECT ['+ @ groupColumn +'], ['+ @ row2column +'], ['+ @ row2columnValue +'] FROM ['+ @ tableName +']) p comment

(SUM (['+ @ row2columnValue +']) FOR ['+ @ row2column +'] IN ('+ @ SQL _col +') AS pvt

Order by pvt. ['+ @ groupColumn +']'

-- PRINT (@ SQL _str)

EXEC (@ SQL _str)

(Figure 5)

(7) In practice, I often encounter the need to filter the data in the basic table before performing row-to-column conversion. The following script will meet your needs, result 6:

-- 6: parameterized dynamic shard row-to-column with conditional Query

-- ===================================================== ======

-- Author: <listen to the wind and rain>

-- Create date: <2014.05.26>

-- Description: <parameterized dynamic sequence row to column, parameterized dynamic sequence row to column with conditional query>

-- Blog:

-- ===================================================== ======

DECLARE @ SQL _str NVARCHAR (MAX)

DECLARE @ SQL _col NVARCHAR (MAX)

DECLARE @ SQL _where NVARCHAR (MAX)

DECLARE @ tableName SYSNAME -- List of rows

DECLARE @ groupColumn SYSNAME -- group field

DECLARE @ row2column SYSNAME -- field of the row-modified Column

DECLARE @ row2columnValue SYSNAME -- the field for changing the column value of a row

SET @ tableName = 'testrows2columns'

SET @ groupColumn = 'username'

SET @ row2column = 'subobject'

SET @ row2columnValue = 'source'

SET @ SQL _where = 'where UserName = '''

-- Obtain possible columns from row data

SET @ SQL _str = N'

SELECT @ SQL _col_out = ISNULL (@ SQL _col_out + '','', '''') + QUOTENAME (['+ @ row2column +'])

FROM ['+ @ tableName +'] '+ @ SQL _where + 'group BY [' + @ row2column + ']'

-- PRINT @ SQL _str

EXEC sp_executesql @ SQL _str, n' @ SQL _col_out NVARCHAR (MAX) output', @ SQL _col_out = @ SQL _col OUTPUT

-- PRINT @ SQL _col

SET @ SQL _str = N'

SELECT * FROM (

SELECT ['+ @ groupColumn +'], ['+ @ row2column +'], ['+ @ row2columnValue +'] FROM ['+ @ tableName +'] '+ @ SQL _where +') p comment

(SUM (['+ @ row2columnValue +']) FOR ['+ @ row2column +'] IN ('+ @ SQL _col +') AS pvt

Order by pvt. ['+ @ groupColumn +']'

-- PRINT (@ SQL _str)

EXEC (@ SQL _str)

(Figure 6)