在SQL Server中将列名更改为日期 [英] Changeing column name to date in SQL server

查看:120
本文介绍了在SQL Server中将列名更改为日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

亲爱的女士/先生,

美好的一天,



我有11列的表,我想更改列名10他们到昨天约会 - 在昨天约会之前.....等



桌子结构是:





---------------------------------------- ---------------------------------------------

名称|第1栏| |第2栏| |第3栏| |第4栏| |第5栏| |第6栏|

------------------------------------------ -------------------------------------------

var1 | 159 | | 200 | | 300 | | 179 | | 410 | | 999 |





PS下面的执行查询后的表结构:



Dear Madam/Sir,
Good day,

I have table with 11 column and I want to change column name of 10 of them to yesterday date - before yesterday date ..... etc

the table structure is :


-------------------------------------------------------------------------------------
name | column 1 | | column 2 | | column 3 | | column 4 | | column 5 | | column 6 |
-------------------------------------------------------------------------------------
var1 | 159 | | 200 | | 300 | | 179 | | 410 | | 999 |


P.S the table structure after excute query below :

SELECT DISTINCT 
   case Name when '123' then 'var1'   when   else name end as name,
        sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -1, GETDATE()) as date) THEN cast (ta.VALUE as float) ELSE 0 END) as getdate ,   
        sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -2, GETDATE()) as date) THEN cast (ta.VALUE as float) ELSE 0 END),
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -3, GETDATE()) as date) THEN cast (ta.VALUE as float) ELSE 0 END)   ,
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -4, GETDATE()) as date) THEN cast (Ta.VALUE as float) ELSE 0 END) ,
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -5, GETDATE()) as date) THEN cast (Ta.VALUE as float) ELSE 0 END)  ,
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -6, GETDATE()) as date) THEN cast (Ta.VALUE as float) ELSE 0 END)   ,
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -7, GETDATE()) as date) THEN cast (Ta.VALUE as float) ELSE 0 END)   ,
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -8, GETDATE()) as date) THEN cast (Ta.VALUE as float) ELSE 0 END)   ,
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -9, GETDATE()) as date) THEN cast (Ta.VALUE as float) ELSE 0 END)   ,
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -10, GETDATE()) as date) THEN cast (Ta.VALUE as float) ELSE 0 END)   

        FROM tabel name 
GROUP BY  name





我尝试过:



现在结果

------ -------------------------------------------------- -----------------------------

name |第1栏| |第2栏| |第3栏| |第4栏| |第5栏| |第6栏|

------------------------------------------ -------------------------------------------

var1 | 159 | | 200 | | 300 | | 179 | | 410 | | 999 |





我们的期望是:

---------- -------------------------------------------------- ------------------------------------------

名字| 20/6/2017 | | 19/6/2017 | | 18/6/2017 | | 17/6/2017 | | 16/6/2017 | | 15/6/2017 |

------------------------------------------ -------------------------------------------------- ----------

var1 | 159 | | 200 | | 300 | | 179 | | 410 | | 999 |







我试图制作各种颜色



What I have tried:

the result now
-------------------------------------------------------------------------------------
name | column 1 | | column 2 | | column 3 | | column 4 | | column 5 | | column 6 |
-------------------------------------------------------------------------------------
var1 | 159 | | 200 | | 300 | | 179 | | 410 | | 999 |


and what we expect is :
------------------------------------------------------------------------------------------------------
name | 20/6/2017 | | 19/6/2017 | | 18/6/2017 | | 17/6/2017 | | 16/6/2017 | | 15/6/2017 |
------------------------------------------------------------------------------------------------------
var1 | 159 | | 200 | | 300 | | 179 | | 410 | | 999 |



I tried to create varible

declare @1  date = DATEADD(dd, -1, GETDATE())





并将此变量的结果作为列名





and make the result of this varible as a column name

sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -2, GETDATE()) as date) THEN cast (ta.VALUE as float) ELSE 0 END) as (Select @1),





我相信它错了,我找不到办法。



感谢advanse。



有一个美好的一天,



I belive its wrong and I couldn't find the way to do it.

thanks in advanse.

have a good day,

推荐答案

我解决了它们谢谢你的帮助。



祝你有个美好的一天。
I solve it guys thank you for your help.

have a good day.


这篇关于在SQL Server中将列名更改为日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆