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'