SQL Query to convert rows to columns dynamically

Krish 81 Reputation points
2022-08-18T17:56:03.303+00:00

Dear Friends,

May i request you to suggest a way to use SQL code like a look up please?

Sample data input :

Table A: with 2 columns

Identifier_Column, data

India,323

India,324

India,325

US,326

US,327

Output : Identifier_Column should come as column name and data as rows

India,US

323,326

324,327

325,null

If we have more values in Identifier_column in Table A, we should get all of them as new columns in the results dynamically and data should be distributed.

I tried to use STR_AGG() and tried to convert the data to comma separated values. but not able to get the data as column names dynamically. Please suggest a way.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,919 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Wilko van de Velde 2,226 Reputation points
    2022-08-19T06:12:52.017+00:00

    I dont think its possible with the given input table. You need a identifier for the rows, for example year:

    DROP TABLE IF EXISTS #TableA  
      
    CREATE TABLE #TableA  
    (  
    	[YEAR] INT,  
    	[Identifier_Column] NVARCHAR(10),   
    	[data] INT  
    )  
      
    INSERT INTO #TableA ([YEAR],[Identifier_Column],[data]) VALUES (2022,'India',323)  
    INSERT INTO #TableA ([YEAR],[Identifier_Column],[data]) VALUES (2021,'India',324)  
    INSERT INTO #TableA ([YEAR],[Identifier_Column],[data]) VALUES (2020,'India',325)  
    INSERT INTO #TableA ([YEAR],[Identifier_Column],[data]) VALUES (2022,'US',326)  
    INSERT INTO #TableA ([YEAR],[Identifier_Column],[data]) VALUES (2021,'US',327)  
      
      
    DECLARE @COLUMNS AS NVARCHAR(MAX)  
    DECLARE @QUERY  AS NVARCHAR(MAX)  
      
    SET @COLUMNS = STUFF((SELECT distinct ',' + QUOTENAME([Identifier_Column])   
                FROM #TableA  
                FOR XML PATH(''), TYPE  
                ).value('.', 'NVARCHAR(MAX)')   
            ,1,1,'')  
      
    	  
      
    set @query = 'SELECT	[YEAR],' + @COLUMNS + '    
    			FROM	#TableA  
    			pivot   
    			(  
    				min([data])  
    				for [Identifier_Column] in (' + @COLUMNS + ')  
    			) p   
    			ORDER BY [YEAR] DESC'   
    	  
    execute(@query)  
    
    1 person found this answer helpful.
    0 comments No comments

  2. CosmogHong-MSFT 23,946 Reputation points Microsoft Vendor
    2022-08-19T08:52:44.357+00:00

    Hi @kirankumar-3620

    1. Use static PIVOT, check this:
        CREATE TABLE #Sample (Identifier_Column VARCHAR(20),data INT)  
        INSERT INTO #Sample VALUES   
        ('India',323),('India',324),('India',325),('US',326),('US',327),  
        ('UK',334),('UK',335),('UK',336),('Japan',337),('Japan',338)  
    
        ;WITH CTE AS  
        (  
         SELECT Identifier_Column,data,ROW_NUMBER()OVER(PARTITION BY Identifier_Column ORDER BY data)AS RNum  
         FROM #Sample  
        )  
        SELECT [India],[US],[UK],[Japan] FROM CTE  
        PIVOT(MAX(data) FOR Identifier_Column IN ([India],[US],[UK],[Japan]))P  
    

    2)To avoid typing Identifier_Columns manually, try using Dynamic PIVOT, check this:

    DECLARE @sql_str VARCHAR(8000)  
    DECLARE @Identifier_Columns VARCHAR(8000)  
    
    SELECT @Identifier_Columns = ISNULL(@Identifier_Columns + ',','') + QUOTENAME(Identifier_Column)   
    FROM #Sample GROUP BY Identifier_Column  
    --PRINT @Identifier_Columns  
    
    SET @sql_str = '  
    ;WITH CTE AS  
    (  
     SELECT Identifier_Column,data,ROW_NUMBER()OVER(PARTITION BY Identifier_Column ORDER BY data)AS RNum  
     FROM #Sample  
    )  
    SELECT '+ @Identifier_Columns +' FROM CTE  
    PIVOT(MAX(data) FOR Identifier_Column IN ('+ @Identifier_Columns +'))P'  
    PRINT (@sql_str)  
    EXEC (@sql_str)  
    GO  
    

    Output:
    232777-image.png

    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.

    1 person found this answer helpful.

  3. Erland Sommarskog 102.4K Reputation points
    2022-08-18T21:55:17.447+00:00

    I have written about how to do dynamic pivot here:
    https://www.sommarskog.se/dynamic_sql.html#pivot

    But note that to do this, you first need to master dynamic SQL , which is the main topic for the article. I sent you directly to the section on dynamic pivot, but if your head starts spinning, you better start from the beginning of the article.

    Dynamic SQL is not a trivial topic.

    0 comments No comments

  4. Isabellaz-1451 3,616 Reputation points
    2022-08-19T07:05:30.68+00:00

    Hi @kirankumar-3620
    Maybe you can use Report Builder , add a matrix to the report
    232766-image.png
    232725-image.png

     create table temptable  
     (identifier_Column varchar(20),  
     data int)  
     select * from temptable  
     insert into temptable  
     select 'India',323  
     UNION ALL  
      select 'India',324  
       UNION ALL  
      select 'India',325  
       UNION ALL  
      select 'US',326  
       UNION ALL  
      select 'US',327  
      
       SELECT * , ROW_NUMBER() OVER(PARTITION BY identifier_Column ORDER BY data)FROM temptable  
    

    Best Regards,
    Isabella

    0 comments No comments