How to combine various rows and generate dynamic columns?

edit

To combine various rows and generate dynamic columns, you can use the pivot function in SQL. Here are the general steps:

1. Identify the columns that you want to use as row values and column headers.

2. Use the pivot function to transform the rows into columns. The pivot function takes four parameters - the table or query to pivot, the column to use as row values, the column to use as column headers, and the column to use as the values in the matrix.

3. Use dynamic SQL to generate the list of column headers. You can do this by querying the distinct values in the column that you want to use as column headers and concatenating them into a string.

Once you have generated the dynamic columns, you can use them in your queries and reports. Here's an example query that combines rows for multiple years and generates dynamic columns for each year:

SELECT *
FROM (
   SELECT year, category, value
   FROM sales_data
) as source_data
PIVOT (
   SUM(value)
   FOR year IN ([2019], [2020], [2021])
) as pivot_data

In this example, the "source_data" subquery contains the rows that we want to pivot. The "year" column will be used as the row values and the "category" column will be used as the column headers. The "value" column will be the values in the matrix.

The "pivot_data" query uses the pivot function to transform the rows into columns. The SUM function is used to aggregate the values in each column. The dynamic columns are generated using the IN keyword and a list of distinct year values that are concatenated into a string using dynamic SQL.