Efficiently convert rows to columns in sql server

Question

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?

Answer 1

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;

You can also go through this Demo for better understanding.

Note: You can use an aggregate function with a CASE expression If you do not want to use the PIVOT function

Refer to this video to understand CASE STATEMENT in detail.

QUERY:

SELECT

MAX(CASE when Column_Name = 'First_Name' then value end) First_Name,

  MAX (CASE when Column_Name = 'Amount' then value end) Amount,

  MAX (CASE when Column_Name = 'Postal_Code' then value end) Postal_Code,

MAX(CASE when Column_Name = 'Last_Name' then value end) Last_Name,

  MAX (CASE when Column_Name = 'Account_Number' then value end) Account_Number

fromMyTable

You can also refer to this Demo.

This can be completed only if you are using multiple joins, but you may need some columns to associate each of the rows which are not in the sample data. The basic syntax is like this:

QUERY:

selectfn.value as First_Name,

a.value as Amount,

pc.value as Postal_Code,

ln.value as Last_Name,

an.value as Account_Number

fromMyTablefn

left join MyTable a

onfn.some_col = a.some_col

anda.Column_Name = 'Amount'

left join MyTable pc

onfn.some_col = pc.some_col

andpc.Column_Name = 'Postal_Code'

left join MyTableln

onfn.some_col = ln.some_col

andln.Column_Name = 'Last_Name'

left join MyTable an

onfn.some_col = an.some_col

andan.Column_Name = 'Account_Number'

To master SQL statements, queries and become proficient in SQL queries, enroll in our industry-recognized SQL course.