Convert Rows to Columns - SQL Server

Question

I have below table in SQL Server 2014.

<table>
    <tr><td>ColumnName</td><td>RowOrdinal</td><td>Value</td></tr>
    <tr><td>EmpName</td><td>0</td><td>John</td></tr>
    <tr><td>EmpTitle</td><td>0</td><td>Engineer</td></tr>
    <tr><td>EmpCity</td><td>0</td><td>Chicago</td></tr>
    <tr><td>EmpName</td><td>1</td><td>Matt</td></tr>
    <tr><td>EmpTitle</td><td>1</td><td>Analyst</td></tr>
    <tr><td>EmpCity</td><td>1</td><td>Boston</td></tr>
</table>

Basically I need to convert rows to columns (maybe using PIVOT), so my output should be as follow:

<table>
    <tr><td>RowOrdinal</td><td>EmpName</td><td>EmpTitle</td><td>EmpCity</td></tr>
    <tr><td>0</td><td>John</td><td>Engineer</td><td>Chicago</td></tr>
    <tr><td>1</td><td>Matt</td><td>Analyst</td><td>Boston</td></tr>
</table>

Please provide, if possible, SQL to achieve this. Thanks.

Answer 1

Simple PIVOT will do the job for you

SELECT RowOrdinal,
       [EmpName],
       [EmpTitle],
       [EmpCity]
FROM   yourtable
       PIVOT (Max(Value)
             FOR columnname IN([EmpName],
                               [EmpTitle],
                               [EmpCity]))pv 

or use Conditional Aggregate

SELECT RowOrdinal,
       EmpName=Max(CASE WHEN ColumnName = 'EmpName' THEN Value END),
       EmpTitle=Max(CASE WHEN ColumnName = 'EmpTitle' THEN Value END),
       EmpCity=Max(CASE WHEN ColumnName = 'EmpCity' THEN Value END),
FROM   yourtable
GROUP  BY RowOrdinal