22,300
社区成员




--方法二:
SELECT * FROM (
SELECT 'Jan' AS m,Jan AS v FROM tableA
UNION ALL SELECT 'Feb',Feb FROM tableA
UNION ALL SELECT 'Mar',Mar FROM tableA
UNION ALL SELECT 'Apr',Apr FROM tableA
UNION ALL SELECT 'May',May FROM tableA
UNION ALL SELECT 'Jun',Jun FROM tableA
UNION ALL SELECT 'Jul',Jul FROM tableA
UNION ALL SELECT 'Aug',Aug FROM tableA
UNION ALL SELECT 'Sep',Sep FROM tableA
UNION ALL SELECT 'Oct',Oct FROM tableA
UNION ALL SELECT 'Nov',Nov FROM tableA
UNION ALL SELECT 'Dec',Dec FROM tableA
) AS t
WHERE t.m=SubString('JanFebMarAprMayJunJulAugSepOctNovDec',Month(GETDATE())*3-2,3)
USE tempdb
GO
IF OBJECT_ID('tableA') IS NOT NULL DROP TABLE tableA
GO
CREATE TABLE tableA(
Jan INT,
Feb INT,
Mar INT,
Apr int,
May int,
Jun int,
Jul int,
Aug int,
Sep int,
Oct INT,
Nov INT,
[Dec] int
)
GO
INSERT INTO tableA
(
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
[Dec]
)
VALUES
(
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12
)
---- 以上为测试表及测试数据
declare @str nvarchar(4000)= (select * from tableA for JSON path)
select [key],[value]
from openjson(SUBSTRING(@str,2,len(@str)-2))
where [key]=SubString('JanFebMarAprMayJunJulAugSepOctNovDec',Month(GETDATE())*3-2,3)
--方法二:
SELECT * FROM (
SELECT 'Jan' AS m,Jan AS v FROM tableA
UNION ALL SELECT 'Feb',Feb FROM tableA
UNION ALL SELECT 'Mar',Mar FROM tableA
UNION ALL SELECT 'Apr',Apr FROM tableA
UNION ALL SELECT 'May',May FROM tableA
UNION ALL SELECT 'Jun',Jun FROM tableA
UNION ALL SELECT 'Jul',Jul FROM tableA
UNION ALL SELECT 'Aug',Aug FROM tableA
UNION ALL SELECT 'Sep',Sep FROM tableA
UNION ALL SELECT 'Oct',Oct FROM tableA
UNION ALL SELECT 'Nov',Nov FROM tableA
UNION ALL SELECT 'Dec',Dec FROM tableA
) AS t
WHERE t.m=SubString('JanFebMarAprMayJunJulAugSepOctNovDec',Month(GETDATE())*3-2,3)
USE tempdb
GO
IF OBJECT_ID('tableA') IS NOT NULL DROP TABLE tableA
GO
CREATE TABLE tableA(
Jan INT,
Feb INT,
Mar INT,
Apr int,
May int,
Jun int,
Jul int,
Aug int,
Sep int,
Oct INT,
Nov INT,
[Dec] int
)
GO
INSERT INTO tableA
(
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
[Dec]
)
VALUES
(
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12
)
---- 以上为测试表及测试数据
DECLARE @sql NVARCHAR(MAX)
DECLARE @m VARCHAR(10)
SELECT @m=SubString('JanFebMarAprMayJunJulAugSepOctNovDec',Month(GETDATE())*3-2,3)
SET @sql='select ['+@m+'] from tableA'
EXEC (@sql);
/*
Oct
------
10
*/