How Data to be displayed

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

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;

Hi

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

Thanks

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!