sql server 2005版本及以上版本行转列函数

2019独角兽企业重金招聘Python工程师标准>>>

-- 创建表
IF OBJECT_ID('VrveisConfigKeyValue',N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[VrveisConfigKeyValue](
        [ID] [INT] IDENTITY(1,1) NOT NULL,
        [KeyName] [VARCHAR](100) NOT NULL,
        [KeyValue] [TEXT] NULL,
        [TypeName] [VARCHAR](100) NOT NULL,
        [UserID] [INT] NOT NULL,
        [ExField1] [INT] NULL,
        [ExField2] [INT] NULL,
        [Reserved1] [VARCHAR](500) NULL,
        [Reserved2] [VARCHAR](500) NULL,
    PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

 

 

 /*
 *
 *<参数化动态PIVOT行转列，带条件查询的参数化动态PIVOT行转列>
 *
 */ 
 
DECLARE @sql_str NVARCHAR(MAX)
 
DECLARE @sql_col NVARCHAR(MAX)
 
DECLARE @sql_where NVARCHAR(MAX)
 
DECLARE @tableName SYSNAME --行转列表
 
DECLARE @showColumns SYSNAME --显示列
 
DECLARE @pivotColumn SYSNAME --行变列的字段
 
DECLARE @pivotColumnValue SYSNAME --行变列值的字段(聚合函数使用)

DECLARE @groupTable SYSNAME -- 行变列列的查询表

DECLARE @groupWhere SYSNAME -- 行变列分组条件

SET @tableName = 'VrveisConfigKeyValue AS v LEFT JOIN dbo.Class AS c ON c.ClassID=v.ExField1'
 
SET @showColumns = 'v.ExField1 AS ClassID,v.KeyName,v.ExField2,c.ClassName'
 
SET @pivotColumn = 'KeyName'
 
SET @pivotColumnValue = 'ExField2'
 
SET @sql_where = 'WHERE v.TypeName=''ZJSELECTVALUE'' AND v.Reserved1=''201805'' '

 --从行数据中获取可能存在的列
SET @groupTable='VrveisConfigKeyValue'

SET @groupWhere='WHERE TypeName=''ZJSELECTVALUE'' AND Reserved1=''201805'' and KeyName <>''AQZSStatistics'' and KeyName<>''YJLYStatistics'' '

SET @sql_str = N'
SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@pivotColumn+'])
FROM ['+@groupTable+'] '+@groupWhere+' GROUP BY ['+@pivotColumn+']'
--PRINT @sql_str
 
EXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT
--PRINT @sql_col  -- 打印行转列的列值

SET @sql_str = N'
  WITH TempDetail
  AS (
    SELECT '+@showColumns+' FROM '+@tableName+'  '+@sql_where+'
  )
  SELECT * FROM TempDetail PIVOT(MAX('+@pivotColumnValue+') FOR '+@pivotColumn+' IN ('+@sql_col+')) AS T 
'
-- PRINT (@sql_str)
EXEC (@sql_str)
 

 

 

 

转载于:https://my.oschina.net/guanxinsui/blog/1809355