Efficiently convert rows to columns in sql server
I'm looking for an efficient way to convert rows to columns in the SQL server, I heard that PIVOT is not very fast, and I need to deal with a lot of records.
This is my example:
-------------------------------
| Id | Value | ColumnName |
-------------------------------
| 1 | John | FirstName |
| 2 | 2.4 | Amount |
| 3 | ZH1E4A | PostalCode |
| 4 | Fork | LastName |
| 5 | 857685 | AccountNumber |
-------------------------------
This is my result:
---------------------------------------------------------------------
| FirstName |Amount| PostalCode | LastName | AccountNumber |
---------------------------------------------------------------------
| John | 2.4 | ZH1E4A | Fork | 857685 |
---------------------------------------------------------------------
How can I build the result? In sql server how to transform data from rows to columns
For sql server rows to columns transformation, there are multiple ways using which you can transform data from multiple rows into multiple columns.
In SQL Server, by using PIVOT function you can transform the data from rows to columns:
QUERY:
select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
select value, columnname
fromyourtable
) d
pivot
(
max(value)
for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;You can also go through this Demo for better understanding.
For an unknown number of column names that you want to transpose, you can use this dynamic SQL mentioned below:
QUERY:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName)
fromyourtable
group by ColumnName, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT ' + @cols + N' from
(
select value, ColumnName
fromyourtable
) x
pivot
(
max(value)
forColumnName in (' + @cols + N')
) p '
execsp_executesql @query;