convert rows into columns

yesubabu k

Dear experts,

kindly help me to get data like below

from

Select customer_number [Customer Number],customer_Name [Customer Name],Collector [Collector],clrno [CLR No],Clrdate [CLR Date],commitment_amount [Commitment Amount]
,Commitment_Date [Commitment Date] ,Receipt_Date [Receipt Date]
From (
Select
cm.customer_number,cm.customer_name,c.name as Collector,h.header_id clrno,Format(h.creation_date ,'dd-MMM-yyyy') Clrdate,cml.commitment_amount
,Format(cml.commitment_date ,'dd-MMM-yyyy') Commitment_Date ,Format(cml.receipt_date ,'dd-MMM-yyyy') Receipt_Date
From CLRHdrs h
Inner join CustomerMasters cm on h.customer_id=cm.customer_id
Inner join ClrCommitmentLogs cml on h.header_id=cml.clr_id
inner join Collectors c on h.collector_id=c.collector_id

Where -- h.header_id=156000

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vishal Joshi2y ago

Accepted answer

Hello,

To convert rows to columns you can use PIOVT. Check the below query for your reference.

SELECT 
    *
FROM 
    (
        SELECT 
            cm.customer_number,
            cm.customer_name,
            c.name as Collector,
            h.header_id clrno,
            Format(h.creation_date ,'dd-MMM-yyyy') Clrdate,
            Format(cml.commitment_date ,'dd-MMM-yyyy') Commitment_Date,
            Format(cml.receipt_date ,'dd-MMM-yyyy') Receipt_Date,
            cml.commitment_amount
        FROM 
            CLRHdrs h
        INNER JOIN 
            CustomerMasters cm ON h.customer_id = cm.customer_id
        INNER JOIN 
            ClrCommitmentLogs cml ON h.header_id = cml.clr_id
        INNER JOIN 
            Collectors c ON h.collector_id = c.collector_id
        WHERE 
            -- h.header_id = 156000 
    ) AS SourceTable
PIVOT 
(
    SUM(commitment_amount)
    FOR Commitment_Date IN ([2024-01-01], [2024-01-02], [2024-01-03] /* Add more dates as needed */)
) AS PivotTable;

Thanks

Vishal Joshi

Mukesh Nailwal2y ago
Hello, you can refer ArunKumar's article on C# Corner to convert rows into columns.. Here is the link

https://www.c-sharpcorner.com/code/1267/converting-rows-into-columns.aspx

Thanks
0

Prasad Raveendran2y ago

Here's an example of how you can modify your query to achieve that:

SELECT 
    customer_number AS [Customer Number],
    customer_name AS [Customer Name],
    Collector,
    clrno AS [CLR No],
    Clrdate AS [CLR Date],
    commitment_amount AS [Commitment Amount],
    Commitment_Date AS [Commitment Date],
    Receipt_Date AS [Receipt Date]
FROM   
    (
        SELECT     
            cm.customer_number,
            cm.customer_name,
            c.name AS Collector,
            h.header_id clrno,
            FORMAT(h.creation_date ,'dd-MMM-yyyy') AS Clrdate,
            cml.commitment_amount,
            FORMAT(cml.commitment_date ,'dd-MMM-yyyy') AS Commitment_Date,
            FORMAT(cml.receipt_date ,'dd-MMM-yyyy') AS Receipt_Date
        FROM   
            CLRHdrs h    
            INNER JOIN CustomerMasters cm ON h.customer_id = cm.customer_id    
            INNER JOIN ClrCommitmentLogs cml ON h.header_id = cml.clr_id    
            INNER JOIN Collectors c ON h.collector_id = c.collector_id    
        WHERE  
            h.header_id = 156000  -- Replace with the desired header_id
    ) AS SourceTable
PIVOT
(
    MAX(commitment_amount)
    FOR clrno IN ([156000], [OtherHeaderId1], [OtherHeaderId2], ...) -- Add other header_ids as needed
) AS PivotTable;

This assumes that commitment_amount is the value you want to pivot and that you want to pivot based on the header_id values. Adjust the column names and values accordingly based on your specific requirements. If you have multiple header_ids, you can add them inside the IN clause of the PIVOT statement.

Rajanikant Hawaldar2y ago
Check below articles, might help you to resolve your issue by understanding through...

https://www.c-sharpcorner.com/code/1267/converting-rows-into-columns.aspx

https://www.c-sharpcorner.com/UploadFile/suthish_nair/convert-rows-to-columns-in-sql-server-20082005/
+1