PCB MS SQL 行转列(动态拼SQL)

 一.原数据：

SELECT inman,indate
FROM [fp_db].[dbo].[ppezhpbb]
WHERE indate > '2016-5-1' AND indate < '2016-6-1'
ORDER BY indate

 

二.转换后（动态拼接SQL）：

--数据先存临时表
SELECT  inman,indate INTO #tab
FROM [fp_db].[dbo].[ppezhpbb]
WHERE indate > '2016-5-1' AND indate < '2016-6-1'
--拼接字符串
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT CONVERT(varchar(100), indate, 23) indate ' 
SELECT @sql = @sql + ' ,sum(case when inman=''' + inman + ''' then 1 else 0 end) ' + inman  --行转列统计
FROM #tab
GROUP BY inman   --需由列转行的字段
SET @sql = @sql + ' FROM [fp_db].[dbo].[ppezhpbb] WHERE indate > ''2016-5-1'' AND indate < ''2016-6-1'' '
SET @sql = @sql + ' GROUP BY CONVERT(varchar(100), indate, 23) '
SET @sql = @sql + ' ORDER BY indate '
SELECT @sql
exec sp_executesql @sql