SQL 问题请教 相对应列名的字段值

[qq_26641201]

有一个表为

列名：w2010, W2011, W2012, W2013, W2014, DATE1,DATE2
100, 200, 200, 230, 440, 2011-01-01,2014-12-31


如何转变成

date1 , 分数
2011-01-01, 200
2012-01-01,200
2013-01-01,230
2014-01-01,440

在日期这个年度识别列名，而找到分数。

[二月十六]

是通过一列(date1)的值和列名中的一部分(W2011)比较显示数据？

[qq_26641201]

引用 1 楼 sinat_28984567 的回复:

是通过一列(date1)的值和列名中的一部分(W2011)比较显示数据？

对！！是的！！ 要date1 到 date2之间的 ，对应年度2012，就要W2012的值，年度2013，就要W2013的值

[吉普赛的歌]

你这些列， 是固定的么？

[qq_26641201]

引用 3 楼 yenange 的回复:

你这些列， 是固定的么？

是固定的

[吉普赛的歌]

你的列是固定的， 就不想用动态sql 了， 直接这么写吧：

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	w2010 INT,
	w2011 INT,
	w2012 INT,
	w2013 INT,
	w2014 INT,
	date1 DATE,
	date2 DATE
)
GO
INSERT INTO t VALUES (100,200,200,230,440,'2011-01-01','2014-12-31')

--
SELECT '2010-01-01' AS date1 , w2010 as score
FROM t WHERE '2010-01-01'>=date1 AND '2010-01-01'<=date2
UNION ALL
SELECT '2011-01-01' AS date1 , w2011 as score
FROM t WHERE '2011-01-01'>=date1 AND '2011-01-01'<=date2
UNION ALL
SELECT '2012-01-01' AS date1 , w2012 as score
FROM t WHERE '2012-01-01'>=date1 AND '2012-01-01'<=date2
UNION ALL
SELECT '2013-01-01' AS date1 , w2013 as score
FROM t WHERE '2013-01-01'>=date1 AND '2013-01-01'<=date2
UNION ALL
SELECT '2014-01-01' AS date1 , w2014 as score
FROM t WHERE '2014-01-01'>=date1 AND '2014-01-01'<=date2

/*
date1      score
---------- -----------
2011-01-01 200
2012-01-01 200
2013-01-01 230
2014-01-01 440  
*/

[qq_26641201]

引用 5 楼 yenange 的回复:

你的列是固定的， 就不想用动态sql 了， 直接这么写吧：

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	w2010 INT,
	w2011 INT,
	w2012 INT,
	w2013 INT,
	w2014 INT,
	date1 DATE,
	date2 DATE
)
GO
INSERT INTO t VALUES (100,200,200,230,440,'2011-01-01','2014-12-31')

--
SELECT '2010-01-01' AS date1 , w2010 as score
FROM t WHERE '2010-01-01'>=date1 AND '2010-01-01'<=date2
UNION ALL
SELECT '2011-01-01' AS date1 , w2011 as score
FROM t WHERE '2011-01-01'>=date1 AND '2011-01-01'<=date2
UNION ALL
SELECT '2012-01-01' AS date1 , w2012 as score
FROM t WHERE '2012-01-01'>=date1 AND '2012-01-01'<=date2
UNION ALL
SELECT '2013-01-01' AS date1 , w2013 as score
FROM t WHERE '2013-01-01'>=date1 AND '2013-01-01'<=date2
UNION ALL
SELECT '2014-01-01' AS date1 , w2014 as score
FROM t WHERE '2014-01-01'>=date1 AND '2014-01-01'<=date2

/*
date1      score
---------- -----------
2011-01-01 200
2012-01-01 200
2013-01-01 230
2014-01-01 440  
*/

列名是固定的，但我只列举了W2011-W2014，如果从W1998，那怎么办，是不是太繁琐了点？

[吉普赛的歌]

那就动态sql 吧， 也没什么难度：

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	w2010 INT,
	w2011 INT,
	w2012 INT,
	w2013 INT,
	w2014 INT,
	date1 DATE,
	date2 DATE
)
GO
SET NOCOUNT ON
INSERT INTO t VALUES (100,200,200,230,440,'2011-01-01','2014-12-31')
GO
------- 以上为测试数据 -------


DECLARE @tableName NVARCHAR(50),@sql NVARCHAR(MAX)
SET @tableName='t'
SELECT @sql=ISNULL(@sql,'')+
'
UNION ALL
SELECT '''+RIGHT(c.name,4)+'-01-01'' AS date1 , '+c.name+' as score
FROM t WHERE '''+RIGHT(c.name,4)+'-01-01''>=date1 AND '''+RIGHT(c.name,4)+'-01-01''<=date2' 
FROM sys.[columns] AS c WHERE c.[object_id]=object_id(@tableName) AND c.name LIKE 'w[1-2][0-9][0-9][0-9]'
SET @sql=STUFF(@sql,1,12,'')
--PRINT @sql
EXEC(@sql)

/*
date1      score
---------- -----------
2011-01-01 200
2012-01-01 200
2013-01-01 230
2014-01-01 440  
*/