SqlServer 动态行转列（个人：触发器、排序功能）

--创建用户成绩表
CREATE TABLE [DBO].[FRACTION](
    [USER_ID]   [INT] IDENTITY(1,1) NOT NULL,
    [USER_NAME] [NVARCHAR](50) NULL,
    [USER_OBJECTS] [NVARCHAR](50) NULL,
    [USER_NUM] [NUMERIC](18, 2) NULL
) ON [PRIMARY]
GO

--创建用户成绩表（动态列名表）
CREATE TABLE [DBO].[FRACTION_PIVOT](
    [NID]   [INT] IDENTITY(1,1) NOT NULL,
    [USER_OBJECTS] [NVARCHAR](50) NULL,
    [USER_ORDER] INT
) ON [PRIMARY]
GO

--创建用户成绩表（触发器）

CREATE TRIGGER TGR_FRACTION_INSERT
ON FRACTION
    AFTER INSERT --插入触发
AS

INSERT INTO FRACTION_PIVOT(USER_OBJECTS,USER_ORDER)
SELECT DISTINCT A.USER_OBJECTS,9999 FROM INSERTED A WITH(NOLOCK)
LEFT JOIN FRACTION_PIVOT B WITH(NOLOCK) ON A.USER_OBJECTS = B.USER_OBJECTS
WHERE B.USER_OBJECTS IS NULL

GO

--插入测试数据
INSERT INTO [FRACTION] ([USER_NAME],[USER_OBJECTS],[USER_NUM]) 
    SELECT N'张三',N'语文',10  UNION ALL
    SELECT N'李四',N'语文',20  UNION ALL
    SELECT N'李四',N'数学',30  UNION ALL
    SELECT N'王五',N'语文',40  UNION ALL
    SELECT N'王五',N'数学',50  UNION ALL
    SELECT N'王五',N'英语',60
GO
    

--设置顺序（语、数、外    USER_ORDER）
UPDATE FRACTION_PIVOT SET USER_ORDER = 2 WHERE USER_OBJECTS = '语文'
UPDATE FRACTION_PIVOT SET USER_ORDER = 3 WHERE USER_OBJECTS = '数学'
UPDATE FRACTION_PIVOT SET USER_ORDER = 4 WHERE USER_OBJECTS = '英语'

--插入（赵六数据）
--INSERT INTO [FRACTION] ([USER_NAME],[USER_OBJECTS],[USER_NUM]) SELECT N'赵六',N'物理',70 
--物理（最前）
--UPDATE FRACTION_PIVOT SET USER_ORDER = 1 WHERE USER_OBJECTS = '物理'

DECLARE @SQL_COL VARCHAR(MAX) 
SELECT  @SQL_COL = ISNULL(@SQL_COL + ',','') + QUOTENAME(USER_OBJECTS) 
    FROM FRACTION_PIVOT GROUP BY USER_OBJECTS,USER_ORDER,NID ORDER BY USER_ORDER,NID
PRINT (@SQL_COL)
DECLARE @SQL_STR VARCHAR(MAX)
SET @SQL_STR = 'SELECT * FROM (' 
SET @SQL_STR = @SQL_STR + ' SELECT [USER_NAME],[USER_OBJECTS],[USER_NUM]'
SET @SQL_STR = @SQL_STR + ' FROM [FRACTION] WHERE 1 = 1'
--SET @SQL_STR = @SQL_STR + ' AND [USER_NAME] = ''张三'''
SET @SQL_STR = @SQL_STR + ' ) P PIVOT '
SET @SQL_STR = @SQL_STR + ' (SUM([USER_NUM]) FOR [USER_OBJECTS] IN ( '+ @SQL_COL +') ) AS PVT'
PRINT (@SQL_STR)
EXEC (@SQL_STR)

DROP TABLE [FRACTION]
DROP TABLE [FRACTION_PIVOT]