how can i convert row into columns name in sql

Jitender Singh 21 Reputation points

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:

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,739 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 77,581 Reputation points MVP

    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.

    0 comments No comments

  2. LiHong-MSFT 9,996 Reputation points

    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)  
    ),CTE2 AS  
     SELECT MONTHTYPE AS MonthYear,Month AS PostMonth,CurrentIns,Total_Self_Pay,  
     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,  
    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,

    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.

    0 comments No comments