大牛们sql把行转列,把空缺的月份补充进去

[work_fei]

把上图数据转换成下图的数据

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')

测试数据

[中国风]

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]')

[二月十六]

语句：

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

结果：

[二月十六]

行列转换问题，网上搜搜很多教程。

[work_fei]

引用 1 楼 roy_88 的回复:

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]')

大牛你好,日期是不固定的,你动态写法只得出了menuname

[中国风]

引用 4 楼 work_fei 的回复:

大牛你好,日期是不固定的,你动态写法只得出了menuname

动态写法结果都是相同的，只得出menuname？ 有一种情况就是某一个月没数据才会存在没有这个日期列，这样的情况需要先动态生成列

[work_fei]

引用 5 楼 roy_88 的回复:

[quote=引用 4 楼 work_fei 的回复:] 大牛你好,日期是不固定的,你动态写法只得出了menuname

动态写法结果都是相同的，只得出menuname？ 有一种情况就是某一个月没数据才会存在没有这个日期列，这样的情况需要先动态生成列 [/quote] 怎样动态生成列呢?请大牛赐教

[中国风]

日期不连续时这样用

--动态写法
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
*/

[中国风]

以上日期列生成是取表test.dates最小和最大 如需把日期作为条件，在生成条件和拼凑语句里同时加条件

[work_fei]

引用 8 楼 roy_88 的回复:

以上日期列生成是取表test.dates最小和最大 如需把日期作为条件，在生成条件和拼凑语句里同时加条件

还是有点小问题:从字符串转换为 datetime 时发生语法错误。

[中国风]

引用 9 楼 work_fei 的回复:

还是有点小问题:从字符串转换为 datetime 时发生语法错误。

检查你的数据格式是否规范 如格式:XXXX-XX

[work_fei]

引用 10 楼 roy_88 的回复:

[quote=引用 9 楼 work_fei 的回复:] 还是有点小问题:从字符串转换为 datetime 时发生语法错误。

检查你的数据格式是否规范 如格式:XXXX-XX[/quote] 还是只有menuname这一列

[Tiger_Zhao]

查找错误数据

SELECT *
  FROM test
 WHERE ISDATE(dates+'-01') = 0