how do i can create columns in sql dynamically?

Jitender Singh 21 Reputation points
2022-04-19T07:48:21.947+00:00

i have one scenerio which i really want to pull it off in SQL. Scenerio: i have one date column PostMonth, i want to create columns based on this column.

suppose i have Postmonth column which contain last 4 month as data (like 01/01/2022,02/01/2022,03/01/2022,04/01/2022) i want to create table which will have all these 4 months as columns names and every time based on getdate() i should have new columns added to it

so the table should look like this below:

![194155-image.png]2

and based on getdate whenever we will have may,2022 as a postmonth(like 05/01/2022) a new columns should be added dynamically to the table, for example:

194201-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,690 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
Accepted answer
  1. Ronen Ariely 15,096 Reputation points
    2022-04-19T17:40:55.21+00:00

    Hi,

    i want to create table

    Do you mean to create a physical table and change the structure of the table each time?!?

    This sound like an awful use of the database and in this case I highly recommend you to pause all your work and go the beginning of designing your system with the help of an architect or at least a DBA with some experience with relational databases.

    If you mean to get a table structure of data which is returned in a query, then please provide queries to create the table and insert some sample data + please describe the expected result SET which you want to get from the query - and we will try to help you write the query which you need :-)

    In first glance, it sound to me like you will need to use Dynamic query using the built-in procedure sp_executesql (you can read about it here) or a solution with PIVOT

    Once you will provide the information I mentioned, we will be able to discuss specific solution, assuming the link to the dynamic query did not solve your needs

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2022-04-19T22:14:12.37+00:00

    I think that what you really want is a table with two keys, the two dates that you have on different axis in your images. That's how you store data in a relational database.

    0 comments
  2. Bert Zhou-msft 3,421 Reputation points
    2022-04-20T01:35:38.697+00:00

    Hi，@Jitender Singh

    Welcome to Microsoft T-SQL Q&A Forum!

    Before you post your request, you'd better explain the relationship between the newly added rows and columns.
    At first glance, it can be done with sp and triggers, but you need to provide some details.
    First of all, whether your metadata only has the PostMonth column, and how to guarantee the value of the new column and row, it is best to provide us with a DDL statement and explain the above content, which will facilitate us to communicate and solve problems more efficiently.

    Best regards,
    Bert Zhou

    0 comments