First of I've got a table like this:
| vID | bID | date | type | value |
|---|---|---|---|---|
| 1 | 100 | 22.01.2021 | o | 250.00 |
| 1 | 110 | 25.01.2021 | c | 100.00 |
| 2 | 120 | 13.02.2021 | o | 400.00 |
| 3 | 130 | 20.02.2021 | o | 475.00 |
| 3 | 140 | 11.03.2022 | c | 75.00 |
| 1 | 150 | 15.03.2022 | o | 560.00 |
To show which values were ordered(o) and charged(c) per Month, I have to like 'generate' columns for each month both ordered and charged in a MSSQL SELECT query. Here is an example table of what I want to get:
| vID | JAN2021O | JAN2021C | FEB2021O | FEB2021C | … | MAR2022O | MAR2022C |
|---|---|---|---|---|---|---|---|
| 1 | 250.00 | 100.00 | 560.00 | ||||
| 2 | 400.00 | ||||||
| 3 | 475.00 | 75.00 |
I need a posibility to join it in a SQL SELECT in addition to some other columns I already have.
Does anyone has an idea and could help me please?
CodePudding user response：
The SQL language has a very strict requirement to know the number of columns in the results and the type of each column at query compile time, before looking at any data in the tables. This applies even to SELECT * and PIVOT queries, where the columns are still determined at query compile time via the table definition (not data) or SQL statement.
Therefore, what you want to do is only possible in a single query if you want to show a specific, known number of months from a base date. In that case, you can accomplish this by specifying each column in the SQL and using date math with conditional aggregation to figure the value for each of the months from your starting point. The PIVOT keyword can help reduce the code, but you're still specifying every column by hand, and the query will still be far from trivial.
If you do not have a specific, known number of months to evaluate, you must do this over several steps:
- Run a query to find out how many months you have.
- Use the result from step 1 to dynamically construct a new statement
- Run the statement constructed in step 2.
There is no other way.
Even then, this kind of pivot is usually better handled in the client code or reporting tool (at the presentation level) than via SQL itself.
It's not as likely to come up for this specific query, but you should also be aware there are certain security issues that can be raised from this kind of dynamic SQL, because some of the normal mechanisms to protect against injection issues aren't available (you can't parameterize the names of the source columns, which are dependent on data that might be user-generated) as you build the new query in step 2.