compile sql server online

Language: Layout:

--Title of this code
--Sql Server 2012 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'

create table vwPersonMainContactDetails 
(PersonID   int,
 ContactMethod varchar(20),
 ContactValue varchar(20))
 
 insert into vwPersonMainContactDetails 
 values
(1,'Email','Bob@abc.com'),
(1,'Mobile','07777 777777'),
(2,'Email','kate@abc.com'),
(2,'Mobile','07766 666666'),
(3,'Email','jo@abc.com'),
(3,'Mobile','07755 555555')

/*If we look at the syntax for Pivot:

SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    ...
    [last pivoted column] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;

Aggregate function in this case can be: max/min
<column being aggregated> :ContactValue
<column that contains the values that will become column headers> = `Email` and `Mobile`
Now in Pivot all columns that are left are considered for grouping. 
and in this case it will be PersonID
so Pivot becomes:*/

SELECT PersonID, -- <non-pivoted column>,
    Email , --[first pivoted column] AS <column name>,
    Mobile--[second pivoted column] AS <column name>,
FROM
    (SELECT PersonID ,ContactValue,ContactMethod 
     from vwPersonMainContactDetails)-- query that produces the data>)
    AS T --<alias for the source query>
PIVOT
(
    max(ContactValue) --<aggregation function>(<column being aggregated>)
FOR [ContactMethod] --<column that contains the values that will become column headers>]
    IN ( [Email],[Mobile]--[first pivoted column], [second pivoted column],
        )
)as pvt
order by PersonID asc
--<optional ORDER BY clause>;

​x
<column that contains the values that will become column headers> = `Email` and `Mobile`
 
--Title of this code
--Sql Server 2012 Express Edition
--Batches are separated by 'go'
​
select @@version as 'sql server version'
​
create table vwPersonMainContactDetails 
(PersonID   int,
 ContactMethod varchar(20),
 ContactValue varchar(20))
 
 insert into vwPersonMainContactDetails 
 values
(1,'Email','Bob@abc.com'),
(1,'Mobile','07777 777777'),
(2,'Email','kate@abc.com'),
(2,'Mobile','07766 666666'),
(3,'Email','jo@abc.com'),
(3,'Mobile','07755 555555')
​
​
/*If we look at the syntax for Pivot:
​
SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    ...
    [last pivoted column] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],

View schema
Execution time: 0.04 sec, rows selected: 4, rows affected: 6, absolute service time: 0.17 sec