SQL Server How to set Date to be my column names in SQL

ccrfmcuu 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(114)

I am working on some projects and suppose I have a table with a column called Date . Now I want to create a new table, with MAX(Date) and its 5 previous months to be my columns.

For example, suppose

MAX(Date) = '2023-05-01'

in my original table, then I want the columns in my new table to be

'2022-12-01', ..., '2023-04-01', '2023-05-01'

which are 6 columns. And I want this script can work for any MAX(Date) , like whatever data I got, I can use this script to get a new table with 6 previous months to be the columns.

I am confused as to how to create this new table for any Date . If anyone has any idea, very appreciated and thanks!

I've tried some dynamic SQL but it seems no work. And I am not quite understand how it can work when creating a new table.

sql-server

来源：https://stackoverflow.com/questions/76561072/how-to-set-date-to-be-my-column-names-in-sql

1条答案

按热度按时间

6ie5vjzr1#

Ignoring the most important point of should you do this...

Setting up some test data:

create table #sourceTable
(
    [Date] date
)

insert into #sourceTable values
('2023-07-01'),
('2023-06-01'),
('2023-05-01'),
('2023-04-01'),
('2023-03-01'),
('2023-02-01'),
('2023-01-01'),
('2022-12-01'),
('2022-11-01'),
('2022-10-01')

As you alluded to, you could use some Dynamic SQL to achieve this:

declare @CurrentMonth nvarchar(10) =   (select convert(varchar,max([Date]),23) from #sourceTable)
declare @LastMonth nvarchar(10) =      (select convert(varchar,dateadd(month,-1,max([Date])),23) from #sourceTable)
declare @ThreeMonthsAgo nvarchar(10) = (select convert(varchar,dateadd(month,-2,max([Date])),23) from #sourceTable)
declare @FourMonthsAgo nvarchar(10) =  (select convert(varchar,dateadd(month,-3,max([Date])),23) from #sourceTable)
declare @FiveMonthsAgo nvarchar(10) =  (select convert(varchar,dateadd(month,-4,max([Date])),23) from #sourceTable)

declare @sql nvarchar(max) = 
    'create table newTable (['+ @CurrentMonth + '] int, [' 
    + @LastMonth + '] int, ['
    + @ThreeMonthsAgo + '] int, ['
    + @FourMonthsAgo + '] int, ['
    + @FiveMonthsAgo + '] int)'

exec (@sql)

This results in a table being created as you wish, with the following SQL command for this particular example data:

create table newTable ([2023-07-01] int, [2023-06-01] int, [2023-05-01] int, [2023-04-01] int, [2023-03-01] int)

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server How to set Date to be my column names in SQL

1条答案

相关问题

热门标签

最新问答