How to dynamically row columns in SQL Server

SQL Server dynamic Row-column (parameterized table name, grouped column, row-column field, field value)

I. Content covered by this article (Contents)

What is involved in this article (Contents)

Background (contexts)

Implementation code (SQL codes)

Method One: Use concatenation SQL, static column field;

Method Two: Use concatenation SQL, dynamic column field;

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

Method Four: Use pivot relational operator, dynamic column field;

Extended reading one: Parameterized table names, grouped columns, row-column fields, field values;

Extended reading two: On the basis of the above add conditional filtering;

Reference documents (References)

Two. Background (contexts)

Its implementation is not a new topic, even have been said to be rotten, many of the online examples have some problems, so I hope you can quickly see the effect of execution, so in the dynamic column based on the table, grouped fields, row-column fields, The value of these four rows to the fixed value of the need to become the real meaning of the parameterization, we need to be based on their own environment, set the value of the parameter, immediately can see the effect of (you can jump directly to: "Parameterized dynamic Pivot Row column" View the specific script code). The row-and-column effect is shown in Figure 1:

(Figure 1: Row-column effect chart)

Three. Implementation code (SQL codes)

(a) First we create a test table, insert test data into it, and return the table record as shown in Figure 2:

--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] null,
    [Subject] [nvarchar ] NULL,
    [Source] [numeric] (0) null
) on [PRIMARY] Go
    
--inserting test data insert INTO
[ Testrows2columns] ([Username],[subject],[source]) 
    Select n ' John ', n ' language ',  UNION all
    select n ' Dick ', n ' math ',  union ALL
    Select n ' Harry ', N ' English ', union ALL
    Select n ' Harry ', n ' Math ',  UNION ALL
    Select N ' Harry ', n ' language ',  union ALL
    Select n ' Dick ', n ' language ', union ALL
    Select n ' John ', N ' English ',
    
go SELECT * FROM [Testrows2columns]

(Figure 2: Sample Data)