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

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)

(ii) first realize row rotation in a static way, as shown in effect 3:

--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 realize row rotation in a dynamic way, which is achieved by splicing SQL, so it is applicable to the database version of SQL Server 2000 and above, as shown in the result 2 returned by executing the script;

--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 special PIVOT and UNPIVOT relational operators to do the conversion between rows and rows. The following is realized in a static way. The effect 4 is shown below:

--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
(figure 4)

(v) modify the static SQL above, so as not to worry about what is stored in the record and what column name needs to be converted. The script is as follows, as shown in effect 4:

--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)
(6) a lot of people in the above step could be enough, but you will find that, when other people get your code, need to constantly modified into his own environment, grouping columns, rows, columns where the name fields, field values that several parameters, logical 5, so, I continue to modify the above script, you only have to set their own parameters can realize transfer line column, 4:

--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)
(figure 5)

(vii) in practical application, I often meet the need to filter the data in the basic table and then conduct row switching. Then the following script will meet your needs. Effect 6 is shown below:

--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/ >
Aite: < http://www.codesocang.com >

- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
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 + '] IN ( ‘+ @sql_col +‘) ) AS pvt 
ORDER BY pvt.[‘+@groupColumn+‘]‘
--PRINT (@sql_str)
EXEC (@sql_str)