Display Data without using Pivot

The PIVOT clause in SQL is used to rotate row-based data into columnar format, making it easier to read and analyze. Let's break down the specific PIVOT statement you mentioned:

PIVOT (
    SUM(Amount)                         -- Aggregation function applied to 'Amount'
    FOR Period_Deptt IN (' + @cols + ') -- Converts distinct Period_Deptt values into columns dynamically
) AS PivotTable

This PIVOT operation transforms row values into columns dynamically based on the Period_Deptt column.

Breaking It Down

1. Aggregation Function (SUM(Amount))

   • This part specifies how to aggregate values when multiple rows fall into the same category.
   • Since we are summarizing financial data, we use SUM(Amount), which ensures that all amounts corresponding to a given Location, Month-Year, and Department are summed.

2. FOR Clause (FOR Period_Deptt IN (...))

   • This is the key transformation step.
   • Period_Deptt is the column containing Month-Year + Department (e.g., "Apr-24_Sale", "Aug-24_Accounts").
   • The IN (...) list specifies which values should become new column headers.
   • The @cols variable holds this dynamic list of unique Month-Year and Department values.

3. Alias (AS PivotTable)

   • This assigns a name to the transformed table so it can be referenced in the FROM clause.

Hi Sarthak

  Below u have used PIVOT

PIVOT (
    SUM(Amount)
    FOR Period_Deptt IN (' + @cols + ')
) AS PivotTable

Thanks

Hii Ramco Ramco

SQL Query (Without PIVOT)

SELECT 
    Location,
    SUM(CASE WHEN FORMAT([Posting Date], 'MMM-yy') = 'Apr-24' AND Deptt = 'Sale' THEN Amount ELSE 0 END) AS [Apr-24_Sale],
    SUM(CASE WHEN FORMAT([Posting Date], 'MMM-yy') = 'Apr-24' AND Deptt = 'Accounts' THEN Amount ELSE 0 END) AS [Apr-24_Accounts],
    SUM(CASE WHEN FORMAT([Posting Date], 'MMM-yy') = 'Aug-24' AND Deptt = 'Sale' THEN Amount ELSE 0 END) AS [Aug-24_Sale],
    SUM(CASE WHEN FORMAT([Posting Date], 'MMM-yy') = 'Aug-24' AND Deptt = 'Accounts' THEN Amount ELSE 0 END) AS [Aug-24_Accounts],
    SUM(CASE WHEN FORMAT([Posting Date], 'MMM-yy') = 'Sep-24' AND Deptt = 'Sale' THEN Amount ELSE 0 END) AS [Sep-24_Sale],
    SUM(CASE WHEN FORMAT([Posting Date], 'MMM-yy') = 'Sep-24' AND Deptt = 'Accounts' THEN Amount ELSE 0 END) AS [Sep-24_Accounts]
FROM YourTable
GROUP BY Location
ORDER BY Location;

Explanation

1. Conditional Aggregation with SUM(CASE WHEN...)
   • Each column represents a specific Month-Year and Department.
   • The CASE statement ensures that only values for the matching condition are summed.
2. FORMAT([Posting Date], 'MMM-yy')
   • Converts the date to a Month-Year format, making it easy to filter.
3. GROUP BY Location
   • Ensures the data is grouped by Location, summing up amounts for each condition.

Hi Sarthak

  I dont want to use PIVOT. I want without PIVOT.

Thanks

You can achieve this using conditional aggregation with CASE statements instead of PIVOT. Below is a dynamic SQL approach to generate the required format without explicitly using PIVOT:

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

-- Step 1: Dynamically generate column headers for each Month-Year and Department combination
SELECT @cols = STRING_AGG(
    QUOTENAME(FORMAT([Posting Date], 'MMM-yy') + '_' + Deptt), ','
) 
FROM (SELECT DISTINCT FORMAT([Posting Date], 'MMM-yy') AS Period, Deptt FROM YourTable) AS t;

-- Step 2: Construct the SQL query dynamically
SET @query = '
SELECT Location, ' + @cols + '
FROM (
    SELECT 
        Location, 
        FORMAT([Posting Date], ''MMM-yy'') + ''_'' + Deptt AS Period_Deptt,
        Amount
    FROM YourTable
) AS SourceTable
PIVOT (
    SUM(Amount) 
    FOR Period_Deptt IN (' + @cols + ')
) AS PivotTable
ORDER BY Location;';

-- Step 3: Execute the query
EXEC sp_executesql @query;

Explanation

1. Generate Column Names Dynamically:

   • Extracts unique combinations of FORMAT([Posting Date], 'MMM-yy') (Month-Year) and Deptt.
   • Uses STRING_AGG() to concatenate them dynamically.

2. Construct Dynamic Query:

   • Uses CASE inside SUM() to conditionally sum amounts for each department and time period.

3. Execute Query:

   • The final query is executed using sp_executesql.

Hi

 I need Sql query

Thanks.

Certainly! It seems like you have a table that contains data related to Posting Date, Location, Amount, and Department. You're looking to transform this data into a structured format without using PIVOT. Based on the provided data, the output table organizes the information by Department and Posting Date.

For example, in the transformed table:

- The columns represent different months and years.

- The rows show the total Amount for each Department within the respective month and year.

To achieve this transformation without using PIVOT, you can utilize SQL queries with conditional aggregation. By grouping the data based on Department and month-year combinations, you can calculate the total Amount for each category dynamically.

If you need a specific SQL query or further explanation on implementing this transformation, feel free to let me know!