SQL Pivot/Transform

Question

Using SQL Server 2012, I have the following table

CREATE TABLE [dbo].[DataField](
    [DisplayName] [varchar](255) NOT NULL,
    [DisplayValue] [varchar](255) NOT NULL
) ON [PRIMARY]

GO

I run this insert query

INSERT INTO dbo.DataField VALUES 
('Column 1', '10'),
('Column 2', '20'),
('Column 1', '30'),
('Column 2', '40'),
('Column 1', '50'),
('Column 2', '60'),
('Column 1', '70'),
('Column 2', '80')

I run this query...

SELECT DisplayName, DisplayValue FROM [dbo].[DataField]

Which returns

Field      |   Value
'Column 1' |    10
'Column 2' |    20
'Column 1' |    30
'Column 2' |    40
'Column 1' |    50
'Column 2' |    60

I would like to write a query that outputs the results like this..

Column 1 | Column 2
10       | 20
30       | 40
50       | 60

Can I do this using Pivot or Unpivot or some other tsql?

Answer 1

In @BogdanSahlean's answer, instead of row_number you may also use dense_rank like:

SELECT z.[Column A], z.[Column B]
FROM (
    SELECT x.DisplayName, x.DisplayValue, x.GroupNum
    FROM (
        SELECT  *
                , (DENSE_RANK() 
                   OVER(PARTITION BY df.DisplayName 
                        ORDER BY df.DataFieldID)) AS GroupNum
        FROM     @t df
    ) x
) y
PIVOT( MAX(y.DisplayValue) FOR y.DisplayName IN ([Column A], [Column B]) ) z;