SQL Server dynamic row to column (parameterized table name, grouping column, row to column field, field value)

I. What is covered in this article (Contents)

1. What is covered in this article (Contents)
2. Background (contexts)
3. Implementation code (SQL Codes)
   1. Method One: Use splicing SQL, static column field;
   2. Method two: Using splicing sql, dynamic column field;
   3. Method Three: Use pivot relational operator, static column field;
   4. Method four: Using pivot relational operators, dynamic column fields;
   5. Extended reading: Parameterized table name, grouping column, row to column field, field value;
   6. Extended Reading II: Adding conditional filtering on the previous basis;
4. References (References)

two. Background (contexts)

Its implementation of the column is not a fresh topic, and even has been said to be rotten, many of the online examples are some problems, so I would like to let everyone quickly see the effect of execution, so in the dynamic column based on the table, group fields, row to column fields, The value of these four row to column fixed required value to become the true meaning of the parameterization, you only need to set the parameter values according to their own environment, you can immediately see the effect (you can jump directly to: "Parameterized dynamic pivot row to column" to see the specific script code). The row to column of 1 shows:

(Figure 1: Row to column)

Three. Implementation code (SQL Codes)

(a) First we create a test table, insert the test data inside, return to table record 2 shows:

-- create the 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]
The GO
The CREATE TABLE [dbo] [TestRows2Columns] (
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar] (50), NULL,
[Subject] [nvarchar] (50), NULL,
[Source] [numeric] NULL (18, 0)
) ON the (PRIMARY)
The GO

-- insert test data
INSERT INTO [TestRows2Columns] ([UserName], [Subject], [Source])
SELECT N 'three',N 'language',60 UNION ALL
SELECT N 'li si',N 'ma',70 UNION ALL
SELECT N 'wang wu',N 'English',80 UNION ALL
SELECT N 'king five',N 'math',75 UNION ALL
SELECT N 'wang wu',N 'Chinese',57 UNION ALL
SELECT N 'li si',N 'Chinese',80 UNION ALL
SELECT N 'zhang SAN',N 'English',100
The GO

SELECT * FROM [TestRows2Columns] 

(Figure 2: Sample Data)

(b) First, a static way to achieve row to column, the effect of 3 is shown:

--1: static splicing and row rotation
SELECT [UserName],
SUM(CASE [Subject] WHEN 'math' THEN [Source] ELSE 0 END) AS 'math',
SUM(CASE [Subject] WHEN 'English' THEN [Source] ELSE 0 END) AS 'English',
SUM(CASE [Subject] WHEN 'Chinese' THEN [Source] ELSE 0 END) AS '[Chinese]'
The FROM [TestRows2Columns]
GROUP BY [UserName]
The GO 

(Figure 3: Sample Data)

(iii) Then dynamically implement row-to-column, which is implemented by splicing SQL, so it is suitable for SQL Server 2000 database version, the execution script returns the results of 2, as shown;

--2: dynamic splicing and row rotation
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 a
SELECT @sql = LEFT(@sql,LEN(@sql)-1) + 'FROM [TestRows2Columns] GROUP BY [UserName]'
PRINT (SQL) @
The EXEC (@ SQL)
The GO 

(iv) After SQL Server 2005, there is a dedicated pivot and UNPIVOT relational operator to do the conversion between rows and columns, the following is implemented in a static manner, the implementation of effect 4 is as follows:

--3: static PIVOT row to column
SELECT *
FROM (SELECT [UserName],
[Subject].
[Source]
The FROM [TestRows2Columns]
) p PIVOT
(SUM ([Source]) FOR [Subject] IN [[math], [English], [Chinese])) AS the PVT
The ORDER BY PVT. [UserName];
The GO 

(Fig. 4)

(v) The above static SQL based on the modification, so as to ignore what is stored in the record, the need to turn into what column name, the script is as follows, the effect of 4 shows:

--4: dynamic PIVOT row to column
DECLARE @ sql_str VARCHAR (8000).
DECLARE @ sql_col VARCHAR (8000).
select@sql_col = ISNULL(@sql_col + ', ', ' ') + QUOTENAME([Subject]) FROM [TestRows2Columns] GROUP BY [Subject]
The SET @ sql_str = '
SELECT * FROM (
SELECT [UserName],[Subject],[Source] FROM [TestRows2Columns]] p PIVOT
(SUM([Source]) FOR [Subject] IN (' + @sql_col + ')) AS PVT
The ORDER BY PVT. [UserName]"
PRINT (@ sql_str)
The EXEC (@ sql_str) 

(vi) Maybe a lot of people come to the above step is enough, but you will find that when others get your code, you need to constantly modify the table name in his own environment, grouping columns, row To column fields, field values of the parameters, as shown in logic 5, so I continue to modify the above script, You just set your own parameters to achieve row-to-column, the effect of 4 is as follows:

--5: parameterized dynamic PIVOT row to column
- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- Author: < listen to the wind and rain >
- the Create date: < 2014.05.26 >
-- Description: < parameterized dynamic PIVOT row to >
Blog: < http://www.cnblogs.com/gaizai/ >
- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
DECLARE @ sql_str NVARCHAR (MAX)
DECLARE @ sql_col NVARCHAR (MAX)
DECLARE @tablename SYSNAME -- row to list
DECLARE @groupcolumn SYSNAME -- group field
DECLARE @row2column SYSNAME -- the field of the row variable column
DECLARE @row2columnvalue SYSNAME -- field of row variable column value
The SET @ tableName = 'TestRows2Columns'
The SET @ groupColumn = "UserName"
The SET @ row2column = "Subject"
The SET @ row2columnValue = "Source"

Get the possible columns from the row data
The 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

The SET @ sql_str = N '
SELECT * FROM (
SELECT [' + @ groupColumn + '], [' + @ row2column + '], [' + @ row2columnValue + '] FROM [' + @ tableName + ']) p PIVOT
(SUM ([' + @ row2columnValue + ']) FOR [' + @ row2column + '] + '(' + @ sql_col)) IN the AS PVT
The ORDER BY PVT. [' + @ groupColumn + '] '
- PRINT (@ sql_str)
The EXEC (@ sql_str) 

(Fig. 5)

(vii) in the actual application, I often encounter the need to filter the base table data and then row to column, then the following script will meet your needs, the effect of 6 is as follows:

--6: parameterized dynamic PIVOT rows with conditional queries
- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- Author: < listen to the wind and rain >
- the Create date: < 2014.05.26 >
-- Description: < parameterized dynamic PIVOT row to column, parameterized dynamic PIVOT row to column > with conditional query
Blog: < http://www.cnblogs.com/gaizai/ >
- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
DECLARE @ sql_str NVARCHAR (MAX)
DECLARE @ sql_col NVARCHAR (MAX)
DECLARE @ sql_where NVARCHAR (MAX)
DECLARE @tablename SYSNAME -- row to list
DECLARE @groupcolumn SYSNAME -- group field
DECLARE @row2column SYSNAME -- the field of the row variable column
DECLARE @row2columnvalue SYSNAME -- field of row variable column value
The SET @ tableName = 'TestRows2Columns'
The SET @ groupColumn = "UserName"
The SET @ row2column = "Subject"
The SET @ row2columnValue = "Source"
set@sql_where = 'WHERE UserName ='

Get the possible columns from the row data
The SET @ sql_str = N '
SELECT @ sql_col_out = ISNULL (@ sql_col_out + ', ' ' ', ' ' ' ') + QUOTENAME ([' + @ row2column + '])
The 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

The SET @ sql_str = N '
SELECT * FROM (
SELECT [' + @ groupColumn + '], [' + @ row2column + '], [' + @ row2columnValue + '] FROM [' + @ tableName + '] '+ +' @ sql_where) p PIVOT
(SUM ([' + @ row2columnValue + ']) FOR [' + @ row2column + '] + '(' + @ sql_col)) IN the AS PVT
The ORDER BY PVT. [' + @ groupColumn + '] '
- PRINT (@ sql_str)
The EXEC (@ sql_str) 

(Fig. 6)

four. References (References)

Using PIVOT and UNPIVOT