0

I am trying to build a dynamically table with columns when we supply a start date

i.e.

Source Table like this:

Income# recurrent income no of recurrences income start date
1 10000 5 2024/01/28

The output required:

Income# income start date 2024-01 2024-02 2024-03 2024-04 2024-05
1 2024/01/28 10000 10000 10000 10000 10000

Please help if anyone has idea of how to build a SQL script. thanks

I tried CTE function, it did not work

Improve this question
5
  • 1
    That's a SQL Anti-Pattern; SQL is a statically and strongly typed language, which includes a query having a fixed (non varying) number of columns. The correct relational database result would be one Row per recurrence. So, why do you think you want a dynamically varying number of columns? Commented Jun 13, 2024 at 10:33
  • it is just a virtual view, not a static dataset. so, dynamically create columns, CROSS JOIN? Commented Jun 13, 2024 at 11:17
  • So what happens if you have two Income# with different income start dates or a different no of recurrences?. Also the you question with the DBMS you are using (SQL Server etc.) Commented Jun 13, 2024 at 14:22
  • Datasets, queries, inline views, views, etc, etc, all have static signatures; the columns, their names, their datatypes, and the number of them, can not vary dynamically. You can only pivot / unpivot to a fixed and predetermined number of columns. Additionally the language itself is designed for normalised data, not for what you're asking for. So I ask again; why do you think you need this? Commented Jun 13, 2024 at 14:50
  • I am trying to use the CROSS APPLY method at least I can get row by row. Commented Jun 14, 2024 at 1:01
Related questions
281
Create a date from day month and year with T-SQL
213
Getting only Month and Year from SQL DATE
1
How to split one date column into two columns and get data from the start date to last date
Load 4 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.