how can i convert row into columns name in sql

Question

hello everyone,

i need to convert one table into anothertable in which i can use one row as column names
here is my sample datatable:

and
i want something like this:

so the monthyear column should replace the column(Month1,Month2,Month3 ....) in which the value is present.
thanks for your help, i really appreciate your help or suggestion

Answer 1

It is not clear to me if you only want to rename the columns, or if you also want to perform some aggregation.

If you only want to rename the columns, I would say the proper way to do this is to return the result set with the generic names, and then return a second result set with the actual names, and use that result set to set the headers in the UI or in the report. If you really want to do this in SQL Server, you can save the result into a temp table and rename columns with sp_rename:

EXEC sp_rename 'tbl.col', 'newcolname', 'COLUMN'

If you also want to apply aggregations, the same idea applies, but I cannot write any query for you. To do that I would need the CREATE TABLE statement for the table(s) and INSERT statement with sample data, and also the expected result given the sample. I cannot copy and paste from screen images.

Answer 2

Hi @Jitender Singh
Please check this query:

;WITH CTE1 AS  
(  
 SELECT T.Month,T.CurrentIns,T.MonthYear,SUM(T.Total_Self_Pay)OVER(PARTITION BY T.Month,T.CurrentIns)AS Total_Self_Pay,C.VALUE,C.MONTHTYPE  
 FROM Your_Table T CROSS APPLY(VALUES(LEFT(DATENAME(Month,[Month]),3)+'-'+SUBSTRING(CAST(Month AS VARCHAR),3,2),Month1),  
                                     (LEFT(DATENAME(Month,[Month]),3)+'-'+SUBSTRING(CAST(Month AS VARCHAR),3,2),Month2),  
                                     (LEFT(DATENAME(Month,[Month]),3)+'-'+SUBSTRING(CAST(Month AS VARCHAR),3,2),Month3),  
                                     (LEFT(DATENAME(Month,[Month]),3)+'-'+SUBSTRING(CAST(Month AS VARCHAR),3,2),Month4),  
                                     (LEFT(DATENAME(Month,[Month]),3)+'-'+SUBSTRING(CAST(Month AS VARCHAR),3,2),Month5),  
                                     (LEFT(DATENAME(Month,[Month]),3)+'-'+SUBSTRING(CAST(Month AS VARCHAR),3,2),Month6))C(MONTHTYPE,VALUE)  
 WHERE VALUE IS NOT NULL  
),CTE2 AS  
(  
 SELECT MONTHTYPE AS MonthYear,Month AS PostMonth,CurrentIns,Total_Self_Pay,  
        MAX([DEC-21])AS[DEC-21],MAX([JAN-22])AS[JAN-22],MAX([FEB-22])AS[FEB-22],MAX([MAR-22])AS[MAR-22],MAX([APR-22])AS[APR-22]  
 FROM CTE1 PIVOT (MAX(VALUE)FOR MonthYear IN([DEC-21],[JAN-22],[FEB-22],[MAR-22],[APR-22]))P  
 GROUP BY Month,MONTHTYPE,CurrentIns,Total_Self_Pay  
)  
SELECT MonthYear,PostMonth,CurrentIns,SUM(Total_Self_Pay)AS Total_Self_Pay,  
       SUM([DEC-21])AS[DEC-21],SUM([JAN-22])AS[JAN-22],SUM([FEB-22])AS[FEB-22],SUM([MAR-22])AS[MAR-22],SUM([APR-22])AS[APR-22]  
FROM CTE2  
GROUP BY GROUPING SETS((MonthYear,PostMonth,CurrentIns),(MonthYear,PostMonth))  

Note :Since you only post some datas in a image, i cannot guarantee that the above query will execute correctly without testing.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.