CODEWITHSUNDEEP
sql-serverazureazure-synapse
Suresh
Suresh

Reputation: 103

Multiple rows to single row in Azure Data Warehouse

As Azure DW is not supporting FOR XML and SELECT variable assignment, is there any other way to convert multiple rows into single row except using CURSOR?

Upvotes: 1

Views: 2243

Answers (1)

Suresh
Suresh

Reputation: 103

I didn't found any direct method however the below code is working for me.

DECLARE @intColumnCount  INT, 
        @intProcessCount INT, 
        @varColList      VARCHAR(max) 

SET @varColList = '' 

IF Object_id('tempdb.dbo.#tempColumnNames') IS NOT NULL 
  BEGIN 
      DROP TABLE #tempcolumnnames; 
  END 

CREATE TABLE #tempcolumnnames 
  ( 
     intid          INT, 
     varcolumnnames VARCHAR(256) 
  ) 

INSERT INTO #tempcolumnnames 
SELECT Row_number() 
         OVER ( 
           ORDER BY NAME), 
       NAME 
FROM   sys.tables 

SET @intProcessCount = 1 
SET @intColumnCount = (SELECT Count(*) 
                       FROM   #tempcolumnnames) 

WHILE ( @intProcessCount <= @intColumnCount ) 
  BEGIN 
      SET @varColList = @varColList + ', ' 
                        + (SELECT varcolumnnames 
                           FROM   #tempcolumnnames 
                           WHERE  intid = @intProcessCount) 
      SET @intProcessCount +=1 
  END 

SELECT Stuff(@varColList, 1, 2, '')

Hope this helps someone.

Upvotes: 3

Related Questions