SQL Server dynamic row to column (download)

I. What is covered in this article (Contents)

What is covered in this article (Contents)

Background (contexts)

Implementation code (SQL Codes)

Method One: Use splicing SQL, static column field;

Method two: Using splicing SQL, dynamic column field;

Method Three: Use pivot relational operator, static column field;

Method four: Using pivot relational operators, dynamic column fields;

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 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 (*) Not NULL,

[UserName] [nvarchar] () NULL,

[Subject] [nvarchar] () NULL,

[Source] [Numeric] (18, 0) Null

) on [PRIMARY]

GO

--Inserting test data

INSERT into [Testrows2columns] ([Username],[subject],[source])

SELECT N ' Zhang San ', N ' language ', UNION all

SELECT n ' John Doe ', n ' math ', + UNION all

SELECT n ' Harry ', N ' English ', UNION all

SELECT n ' Harry ', n ' math ', UNION all

SELECT n ' Harry ', n ' language ', the UNION all

SELECT n ' John Doe ', n ' language ', UNION all

SELECT N ' Zhang San ', N ' English ', 100

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 row to column

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 ' language ' then [Source] ELSE 0 END) as ' [language] '

from [Testrows2columns]

GROUP by [UserName]

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 row to column

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)

EXEC (@sql)

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]

from [Testrows2columns]

) P PIVOT

(SUM ([Source]) for [Subject] in ([Math],[English],[language])) As Pvt

ORDER by Pvt. [UserName];

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]

SET @sql_str = '

SELECT * FROM (

SELECT [Username],[subject],[source] from [Testrows2columns]) p PIVOT

(SUM ([Source]) for [Subject] in (' + @sql_col + ')) As Pvt

ORDER by Pvt. [UserName] '

PRINT (@sql_str)

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: < Listening to the wind and blowing the rain >

--Create Date: <2014.05.26>

--Description: < parametric dynamic pivot row to column >

--Blog:

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

DECLARE @sql_str NVARCHAR (MAX)

DECLARE @sql_col NVARCHAR (MAX)

DECLARE @tableName SYSNAME--line-to-go list

DECLARE @groupColumn SYSNAME--Group field

DECLARE @row2column SYSNAME--Row Variable column field

DECLARE @row2columnValue SYSNAME--field for row variable column values

SET @tableName = ' Testrows2columns '

SET @groupColumn = ' UserName '

SET @row2column = ' Subject '

SET @row2columnValue = ' Source '

--get columns that may exist from the row data

SET @sql_str = N '

SELECT @sql_col_out = ISNULL (@sql_col_out + "," "," ") + QUOTENAME ([' [email protected]+ '])

from [' [E-mail protected]+ '] GROUP by [' [email protected]+ '] '

--print @sql_str

EXEC sp_executesql @sql_str, N ' @sql_col_out NVARCHAR (MAX) output ', @[email protected]_col output

--print @sql_col

SET @sql_str = N '

SELECT * FROM (

SELECT [' [Email protected]+ '],[' [email protected]+ '],[' [email protected]+ '] from [' [email protected]+ '] ' p PIVOT

(SUM ([' [email protected]+ ']) for [' [E-mail protected]+ '] in (' + @sql_col + ')) As Pvt

ORDER by Pvt. [' [Email protected]+ '] '

--print (@sql_str)

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 row to column with conditional query

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

--Author: < Listening to the wind and blowing the rain >

--Create Date: <2014.05.26>

--Description: < parameterized dynamic pivot row to column, parameterized dynamic pivot 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--line-to-go list

DECLARE @groupColumn SYSNAME--Group field

DECLARE @row2column SYSNAME--Row Variable column field

DECLARE @row2columnValue SYSNAME--field for row variable column values

SET @tableName = ' Testrows2columns '

SET @groupColumn = ' UserName '

SET @row2column = ' Subject '

SET @row2columnValue = ' Source '

SET @sql_where = ' where UserName = ' Harry '

--get columns that may exist from the row data

SET @sql_str = N '

SELECT @sql_col_out = ISNULL (@sql_col_out + "," "," ") + QUOTENAME ([' [email protected]+ '])

from [' [E-mail protected]+ '] ' [email protected]_where+ ' GROUP by [' [email protected]+ '] '

--print @sql_str

EXEC sp_executesql @sql_str, N ' @sql_col_out NVARCHAR (MAX) output ', @[email protected]_col output

--print @sql_col

SET @sql_str = N '

SELECT * FROM (

SELECT [' [Email protected]+ '],[' [email protected]+ '],[' [email protected]+ '] from [' [email protected]+ '] ' [email Protected]_where+ ') P PIVOT

(SUM ([' [email protected]+ ']) for [' [E-mail protected]+ '] in (' + @sql_col + ')) As Pvt

ORDER by Pvt. [' [Email protected]+ '] '

--print (@sql_str)

EXEC (@sql_str)

SQL Server dynamic row to column (download)