sql 行列转换
0
最佳答案
1
SELECT * FROM (
SELECT SUBSTRING(CONVERT(varchar(100), CreateTime, 23),1,7) yearmonth,COUNT(1) cnt
FROM dbo.SpecCustomField GROUP BY SUBSTRING(CONVERT(varchar(100), CreateTime, 23),1,7)
)s PIVOT(MAX(cnt) FOR yearmonth in ([2015-06],[2015-07]))t
奖励园豆：5
PIVOT(MAX(cnt) FOR yearmonth in ([2015-06],[2015-07]))t
PIVOT(MAX(cnt) FOR yearmonth in ([2015-06],[2015-07]))t
PIVOT(MAX(cnt) FOR yearmonth in ([2015-06],[2015-07]))t
列[2015-06],[2015-07] 怎么改成动态的，
PIVOT(MAX(cnt) FOR yearmonth in ('['+CONVERT(varchar(100), CreateTime, 23),1,7)+']',)t
这样拼接不对啊
@风吹一点云: in ([2015-06],[2015-07])里[2015-06]是列名 不能写成in ('[2015-06]')的形式
如果要动态的话 可以拼sql来实现
DECLARE @yearmonth VARCHAR(200)
SET @yearmonth='[2015-06],[2015-07]' --自己拼就好了
DECLARE @sql NVARCHAR(1000)
SET @sql=N'
SELECT * FROM (
SELECT SUBSTRING(CONVERT(varchar(100), CreateTime, 23),1,7) yearmonth,COUNT(1) cnt
FROM tbl GROUP BY SUBSTRING(CONVERT(varchar(100), CreateTime, 23),1,7)
)s PIVOT(MAX(cnt) FOR yearmonth in ('+@yearmonth+'))t'
EXEC(@sql)
