TSQL Pivot with strings

Question

My apologies, as I know this is a question that's been answered many times in various contexts. However, after an hour of trying and failing to adapt the examples I've found to my needs, I'm at the conclusion that I'm an idiot, and need help specific to my data...

I have a view that returns data as follows:

SELECT * 
FROM vwPersonMainContactDetails

Output:

PersonID  | ContactMethod   | ContactValue
----------+-----------------+-----------------
1           Email             [email protected]
1           Mobile            07777 777777
2           Email             [email protected]
2           Mobile            07766 666666
3           Email             [email protected]
3           Mobile            07755 555555

What I need is to return the data structured as follows:

PersonID  |  Mobile        |  Email
----------+----------------+--------------------------
1            07777 777777     [email protected]
2            07766 666666     [email protected]
3            07755 555555     [email protected]

Can anyone help? I know that PIVOT will be the answer, but I'm really struggling to make it work for me...

Thanks a lot

Andrew

Answer 1

If you are using SQL Server 2005+ you can do this:

SELECT
    *
FROM
(
SELECT
    PersonID,
    ContactMethod,
    ContactValue
FROM
    vwPersonMainContactDetails
) AS SourceTable
PIVOT
(
    MAX(ContactValue)
    FOR ContactMethod IN ([Email],[Mobile])
) AS pvt

If you are not using mssql you can do this:

SELECT
    PersonID,
    MAX(CASE WHEN ContactMethod='Mobile' THEN ContactValue ELSE NULL END) AS Mobile,
    MAX(CASE WHEN ContactMethod='Email' THEN ContactValue ELSE NULL END) AS Email
FROM
    vwPersonMainContactDetails
GROUP BY
    PersonID

Reference:

• Using PIVOT and UNPIVOT

Answer 2

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>;

1. Aggregate function in this case can be: max/min
2. column being aggregated :ContactValue = Email and Mobile

3. Now in Pivot all columns that are left in source table (here T)are considered for grouping and in this case it will be PersonID so Pivot becomes:

   SELECT PersonID, -- <non-pivoted column>, Mobile , --[first pivoted column] AS <column name>, Email--[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 ( [Mobile],[Email]--[first pivoted column], [second pivoted column], ) )as pvt order by PersonID asc --<optional ORDER BY clause>; DEMO