CODEWITHSUNDEEP
sql-serverpivot-table
Anshuman Jasrotia
Anshuman Jasrotia

Reputation: 3185

How to convert columns into rows in sql server for a table with large number of records?

The structure for the table is below:

Id      LanguageId  ResourceName                ResourceValue
1       1           AboutUs                     About us
2       1           Account.AccountActivation   Account activation
8919    5           AboutUs                     Über uns
8920    5           Account.AccountActivation   Konto-Aktivierung

I want the result to be: 

ResourceName               ResourceValue1           ResourceValue2
AboutUs                    About us                 Über uns    
Account.AccountActivation  Account activation       Konto-Aktivierung

What would be the optimal way of doing this. The total number of records in the table is 53157 so I need this to be fast. If any other information is needed then please let me know.

Thanks for your time. :)

Upvotes: 0

Views: 61

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Using Pivot you can get the result.

CREATE TABLE #temp
  (Id            INT,LanguageId    INT,ResourceName  VARCHAR(100),ResourceValue VARCHAR(100))

INSERT INTO #temp
VALUES     ( 1,1,'AboutUs','About us'),(2,1,'Account.AccountActivation','Account activation'),
            (8919,5,'AboutUs','Über uns'),(8920,5,'Account.AccountActivation','Konto-Aktivierung')

DECLARE @cols    VARCHAR(max)='',
        @aggcols VARCHAR(max)='',
        @sql     NVARCHAR(max)

SELECT @cols += rr
FROM   (SELECT DISTINCT ',[' + 'Resource'+ CONVERT(VARCHAR(25), Dense_rank() OVER (PARTITION BY id1 ORDER BY languageid))+ ']' rr
        FROM   (SELECT 1 AS id1,*
                FROM   #temp)bb) cc

SELECT @aggcols += rr
FROM   (SELECT DISTINCT ',max([' + 'Resource'+ CONVERT(VARCHAR(25), Dense_rank() OVER (PARTITION BY id1 ORDER BY languageid))+ '])' + '[Resource'
                        + CONVERT(VARCHAR(25), Dense_rank() OVER (PARTITION BY id1 ORDER BY languageid))+ ']' rr
        FROM   (SELECT 1 AS id1,*
                FROM   #temp)bb) cc

SELECT @cols = RIGHT(@cols, Len(@cols) - 1)
SELECT @aggcols = RIGHT(@aggcols, Len(@aggcols) - 1)

SET @sql='  SELECT ResourceName,' + @aggcols+ '
            FROM  (SELECT ''Resource''+ CONVERT(VARCHAR(25), Dense_rank() OVER (PARTITION BY id1 ORDER BY languageid)) rn,
                        *
            FROM  (SELECT 1 AS id1,*
                  FROM   #temp)bb)a
            PIVOT ( Max(ResourceValue)
                FOR rn IN (' + @cols + ') )piv
            GROUP  BY ResourceName '

EXEC Sp_executesql @sql

OUTPUT :

ResourceName                Resource1            Resource2
-------------------------   ------------------   -----------------
AboutUs                     About us             Über uns
Account.AccountActivation   Account activation   Konto-Aktivierung

Upvotes: 2

Related Questions