22,296
社区成员
发帖
与我相关
我的任务
分享大牛们sql把行转列,把空缺的月份补充进去
把上图数据转换成下图的数据
CREATE TABLE [test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[MenuName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[score] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[dates] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into test values('a','20','2016-12')
insert into test values('b','20','2016-12')
insert into test values('a','20','2017-01')
insert into test values('b','20','2017-01')
insert into test values('c','20','2017-01')
insert into test values('c','20','2017-02')
测试数据
...全文
请发表友善的回复…
发表回复
Tiger_Zhao 2017-02-14
- 打赏
- 举报
查找错误数据
SELECT *
FROM test
WHERE ISDATE(dates+'-01') = 0
work_fei 2017-02-14
- 打赏
- 举报
检查你的数据格式是否规范
如格式:XXXX-XX[/quote]
还是只有menuname这一列
中国风 2017-02-14
- 打赏
- 举报
检查你的数据格式是否规范
如格式:XXXX-XX
work_fei 2017-02-14
- 打赏
- 举报
还是有点小问题:从字符串转换为 datetime 时发生语法错误。
中国风 2017-02-14
- 打赏
- 举报
以上日期列生成是取表test.dates最小和最大
如需把日期作为条件，在生成条件和拼凑语句里同时加条件
中国风 2017-02-14
- 打赏
- 举报
日期不连续时这样用
--动态写法
GO
DECLARE @Sql NVARCHAR(max)=''
SELECT @Sql=@Sql+',MAX(CASE WHEN [dates]='''+dates+''' THEN score END) AS '+QUOTENAME(dates) FROM (SELECT CONVERT(VARCHAR(7),DATEADD(mm,b.number,a.d1+'-01'),120) FROM (SELECT MIN(dates) AS d1,MAX(dates) AS d2 FROM test) AS a,master.dbo.spt_values AS b WHERE b.type='P' AND DATEADD(mm,b.number,a.d1+'-01')<=a.d2+'-01') AS a([dates])
EXEC('SELECT [MenuName]'+@Sql+' FROM test GROUP BY [MenuName]')
/*
MenuName 2016-12 2017-01 2017-02
a 20 20 NULL
b 20 20 NULL
c NULL 20 20
*/
work_fei 2017-02-14
- 打赏
- 举报
动态写法结果都是相同的，只得出menuname？
有一种情况就是某一个月没数据才会存在没有这个日期列，这样的情况需要先动态生成列
[/quote]
怎样动态生成列呢?请大牛赐教
中国风 2017-02-14
- 打赏
- 举报
动态写法结果都是相同的，只得出menuname？
有一种情况就是某一个月没数据才会存在没有这个日期列，这样的情况需要先动态生成列
work_fei 2017-02-14
- 打赏
- 举报
大牛你好,日期是不固定的,你动态写法只得出了menuname
二月十六 2017-02-14
- 打赏
- 举报
行列转换问题，网上搜搜很多教程。
二月十六 2017-02-14
- 打赏
- 举报
语句：
结果：
SELECT MenuName ,
MAX(CASE WHEN dates = '2016-12' THEN score
ELSE 0
END) AS '2016-12' ,
MAX(CASE WHEN dates = '2017-01' THEN score
ELSE 0
END) AS '2017-01' ,
MAX(CASE WHEN dates = '2017-02' THEN score
ELSE 0
END) AS '2017-02'
FROM dbo.test
GROUP BY MenuName结果：
中国风 2017-02-14
- 打赏
- 举报
e.g.
USE tempdb
GO
CREATE TABLE [test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[MenuName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[score] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[dates] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into test values('a','20','2016-12')
insert into test values('b','20','2016-12')
insert into test values('a','20','2017-01')
insert into test values('b','20','2017-01')
insert into test values('c','20','2017-01')
insert into test values('c','20','2017-02')
GO
SELECT [MenuName]
,MAX(CASE WHEN [dates]='2016-12' THEN score END) AS [2016-12]
,MAX(CASE WHEN [dates]='2017-01' THEN score END) AS [2017-01]
,MAX(CASE WHEN [dates]='2017-02' THEN score END) AS [2017-02]
FROM test
GROUP BY [MenuName]
/*
MenuName 2016-12 2017-01 2017-02
a 20 20 NULL
b 20 20 NULL
c NULL 20 20
*/
GO
--动态写法
DECLARE @Sql NVARCHAR(max)=''
SELECT @Sql=@Sql+',MAX(CASE WHEN [dates]='''+dates+''' THEN score END) AS '+QUOTENAME(dates) FROM test GROUP BY [dates]
EXEC('SELECT [MenuName]'+@Sql+' FROM test GROUP BY [MenuName]')
