SQL Query to convert rows to columns dynamically

Question

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.

Answer 1

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)  

Answer 2

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:

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.

Answer 3

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.

Answer 4

Hi @kirankumar-3620
Maybe you can use Report Builder ， add a matrix to the report

 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