在SQL Server中将列名更改为日期 [英] Changeing column name to date in 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屋!