Display Data without using Pivot

Question

Hi

I have data like below. I dont want to use PIVOT. Departments are Dynamic. There can be more Departments.

Posting Date	Location	Amount	Deptt
11/8/2024	1	149,435.00	Accounts
24-08-2024	1	62,891.00	Accounts
25-09-2024	1	133,938.00	Accounts
26-09-2024	1	260,056.00	Accounts
7/4/2024	1	1,416.00	Sale
30-04-2024	1	15,115.80	Sale
12/4/2024	2	9,546.20	Sale
28-04-2024	2	9,148.00	Sale

I want Data to be displayed like below without using Pivot

	Apr-24	Apr-24	Aug-24	Aug-24	Sep-25	Sep-24
	Sale	Accounts	Sale	Accounts	Sale	Accounts
1	16531	0	0	212326	0	393994
2	18694	0

Thanks

Sarthak Varshney · Answer

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 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. 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. Alias ( AS PivotTable ) This assigns a name to the transformed table so it can be referenced in the FROM clause.

Sarthak Varshney · Answer

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 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. FORMAT([Posting Date], 'MMM-yy') Converts the date to a Month-Year format, making it easy to filter. GROUP BY Location Ensures the data is grouped by Location , summing up amounts for each condition.

Sarthak Varshney · Answer

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 Generate Column Names Dynamically: Extracts unique combinations of FORMAT([Posting Date], 'MMM-yy') (Month-Year) and Deptt . Uses STRING_AGG() to concatenate them dynamically. Construct Dynamic Query: Uses CASE inside SUM() to conditionally sum amounts for each department and time period. Execute Query: The final query is executed using sp_executesql .

Ramco Ramco · Answer

Hi Sarthak Below u have used PIVOT PIVOT ( SUM(Amount) FOR Period_Deptt IN (' + @cols + ') ) AS PivotTable Thanks

Ramco Ramco · Answer

Hi Sarthak I dont want to use PIVOT. I want without PIVOT. Thanks

Ramco Ramco · Answer

Hi I need Sql query Thanks.

Eliana Blake · Answer

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!

Display Data without using Pivot

Answers (7)