CODEWITHSUNDEEP
sqlsql-serverstringt-sqlpivot
Andrew Richards
Andrew Richards

Reputation: 325

TSQL Pivot with strings

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

Upvotes: 3

Views: 1504

Answers (3)

John Bell
John Bell

Reputation: 2350

Another way for readability:

SELECT PersonID, [Email], [Mobile]
FROM [TABLE]
PIVOT (MAX(ContactValue) FOR ContactMethod IN ([Email], [Mobile])) AS PIV

Upvotes: 0

Deepshikha
Deepshikha

Reputation: 10264

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

Upvotes: 1

Arion
Arion

Reputation: 31239

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:

Upvotes: 2

Related Questions