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

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

2014-05-26 16:09 by listening to the wind blowing rain, 26798 reading, 50 Reviews, Favorites, compilation

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 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] Gocreate TABLE [dbo]. [Testrows2columns] (    [Id] [int] IDENTITY () not NULL,    [UserName] [nvarchar] () null,    [Subject] [nvarchar] () null,    [ Source] [numeric] (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 ', ' All '    select n ' Harry ', N ' English ', '  union ALL '    select n ' Harry ', n ' math ' , the  union all    Select n ' Harry ', n ' language ', the  union all    Select n ' John Doe ', n ' language ', the  union all    Select n ' Zhang San ', N ' English ', 100GOSELECT * 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     [testrows2c Olumns]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 stitching 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 [testr Ows2columns]) as a     SELECT @sql = Left (@sql, LEN (@sql)-1) + ' from [Testrows2columns] GROUP by [UserName] '   PRINT (@sq L) 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 Pvtorder 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 + ', ', ') + Q Uotename ([Subject]) from [Testrows2columns] GROUP by [Subject]set @sql_str = ' select * FROM (    SELECT [Username],[subje Ct],[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: < Listen to wind blowing rain >--Create Date: <2014.05 .26>--Description: < parameterized dynamic pivot row to column >--Blog: 
(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: < Listen to wind blowing rain >--Create Date: <2 014.05.26>--Description: < parameterized dynamic pivot row to column, parameterized dynamic pivot row to column with conditional query >--Blog: 
SQL Server dynamic row to column (parameterized table name, grouping column, row to column field, field value)