How Data to be displayed

Question

Hi

I have below data

Item	Amount	Catg

A001	100	Accounts
A002	200	Accounts
A002	400	Accounts
A003	600	Hr
A004	800	Sales

I want ti display like it below

Item	Amount	Accounts	HR	Sales

A001	100	100
A002	200	600
A003	600		600
A004	800			800

Thanks

Jignesh Kumar · Answer

Hello Ramco, instead of STRING_AGG() function you can use STUFF() in SQL 2014. DECLARE @ColumnNames NVARCHAR(MAX), @SQLQuery NVARCHAR(MAX); -- Step 1: Get distinct categories dynamically (SQL 2014 compatible) SELECT @ColumnNames = STUFF(( SELECT DISTINCT ',' + QUOTENAME(Catg) FROM SourceTable FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); -- Step 2: Construct the dynamic SQL query SET @SQLQuery = ' SELECT ItemCode, LineTotal, ' + @ColumnNames + ' FROM ( SELECT T1."ItemCode", T1."LineTotal", T2."U_A_M" AS Catg FROM Opch T0 INNER JOIN Pch1 T1 ON T0."Docentry" = T1."DocEntry" INNER JOIN Oitm T2 ON T1."ItemCode" = T2."ItemCode" WHERE T2."U_A_M" ''NA'' ) AS SourceTable PIVOT ( SUM(LineTotal) FOR Catg IN (' + @ColumnNames + ') ) AS PivotTable; '; -- Step 3: Execute the dynamic SQL EXEC sp_executesql @SQLQuery;

Ramco Ramco · Answer

Hi Jignesh Below is the code. I need it in Sql 2014 DECLARE @ColumnNames NVARCHAR(MAX), @SQLQuery NVARCHAR(MAX); -- Step 1: Get distinct categories dynamically SELECT @ColumnNames = STRING_AGG(QUOTENAME(Catg), ',') FROM (SELECT DISTINCT Catg FROM SourceTable) AS CategoryList; -- Step 2: Construct the dynamic SQL query SET @SQLQuery = ' SELECT Item, Amount, ' + @ColumnNames + ' FROM ( SELECT T1."ItemCode",T1."LineTotal", T0."U_A_M" FROM Opch T0 inner join Pch1 T1 on T0."Docentry" = T1."DocEntry" inner join Oitm T2 on T1."ItemCode" = T2."ItemCode" where T2."U_A_M" "NA" ) AS SourceTable PIVOT ( SUM(Amount) FOR Catg IN (' + @ColumnNames + ') ) AS PivotTable; '; -- Step 3: Execute the dynamic SQL EXEC sp_executesql @SQLQuery; Thanks

Jignesh Kumar · Answer

Hello Ramco, You can create dynamic pivot table to get requested data as below, DECLARE @ColumnNames NVARCHAR(MAX), @SQLQuery NVARCHAR(MAX); -- Step 1: Get distinct categories dynamically SELECT @ColumnNames = STRING_AGG(QUOTENAME(Catg), ',') FROM (SELECT DISTINCT Catg FROM YourTable) AS CategoryList; -- Step 2: Construct the dynamic SQL query SET @SQLQuery = ' SELECT Item, Amount, ' + @ColumnNames + ' FROM ( SELECT Item, Amount, Catg FROM YourTable ) AS SourceTable PIVOT ( SUM(Amount) FOR Catg IN (' + @ColumnNames + ') ) AS PivotTable; '; -- Step 3: Execute the dynamic SQL EXEC sp_executesql @SQLQuery;

Ramco Ramco · Answer

Hi Columns like Accounts , HR , Sales to be dynamic. There can be say another column to be added :Production" Thanks

Daniel Wright · Answer

Hi there! It looks like you have a structured data table that you want to transform into a more organized format to display the information better. In the initial table, each row represents an item with its corresponding amount and category. The goal is to pivot this data, so each category (Accounts, HR, Sales) has its column to showcase the total amount for each item under that category. To achieve this, we can summarize the data by grouping it based on the categories and then aggregating the amounts. Here's a breakdown of how the transformation can be done: 1. A001 : - Accounts : $100 2. A002 : - Accounts : $600 3. A003 : - HR : $600 4. A004 : - Sales : $800 By restructuring the data this way, you can create a new table with columns for each category ( Accounts**, **HR**, **Sales ). Each item will then have its corresponding amount under the respective category heading, making it easier to analyze and understand the data at a glance. You can use tools like Microsoft Excel, Google Sheets, or coding languages like Python with libraries such as Pandas to perform this data transformation. Let me know if you need further assistance with the implementation or have any specific questions. I'm here to help!

How Data to be displayed

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (5)