Efficiently convert rows to columns in sql server -

i'm looking efficient way convert rows columns in sql server, heard pivot not fast, , need deal lot of records.

this example:

   ----------------------------    | id | value |columnname   |    ----------------------------    | 1  |john   |firstname    |    | 2  |2.4    | amount      |    | 3  |zh1e4a |postalcode   |    | 4  |fork   |lastname     |    | 5  |857685 |accountnumber|    ---------------------------- 

this result:

-------------------------------------------------------------------- |firstname  |amount|    postalcode   |   lastname  |  accountnumber| -------------------------------------------------------------------- |john       | 2.4  |   zh1e4a        |   fork      | 857685        | -------------------------------------------------------------------- 

how can build result?

there several ways can transform data multiple rows columns. in sql server can use pivot function transform data rows columns:

select firstname, amount, postalcode, lastname, accountnumber (   select value, columnname   yourtable ) d pivot (   max(value)   columnname in (firstname, amount, postalcode, lastname, accountnumber) ) piv; 

see demo.

if have unknown number of columnnames want transpose, can use dynamic sql:

declare @cols nvarchar(max),     @query  nvarchar(max)  select @cols = stuff((select ',' + quotename(columnname)                      yourtable                     group columnname, id                     order id             xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')  set @query = n'select ' + @cols + n'               (                 select value, columnname                 yourtable             ) x             pivot              (                 max(value)                 columnname in (' + @cols + n')             ) p '  exec sp_executesql @query; 

see demo.

if not want use pivot function, can use aggregate function case expression:

select   max(case when columnname = 'firstname' value end) firstname,   max(case when columnname = 'amount' value end) amount,   max(case when columnname = 'postalcode' value end) postalcode,   max(case when columnname = 'lastname' value end) lastname,   max(case when columnname = 'accountnumber' value end) accountnumber yourtable 

see demo.

this completed using multiple joins, need column associate each of rows not have in sample data. basic syntax be:

select fn.value firstname,   a.value amount,   pc.value postalcode,   ln.value lastname,   an.value accountnumber yourtable fn left join yourtable   on fn.somecol = a.somecol   , a.columnname = 'amount' left join yourtable pc   on fn.somecol = pc.somecol   , pc.columnname = 'postalcode' left join yourtable ln   on fn.somecol = ln.somecol   , ln.columnname = 'lastname' left join yourtable   on fn.somecol = an.somecol   , an.columnname = 'accountnumber' fn.columnname = 'firstname'