CODEWITHSUNDEEP
sqlsql-serverpivot
user1229404
user1229404

Reputation: 49

Display a column multiple times in SQL Server query

I need some help with a sql query.

I've two tables USERS and ENDPOINT. For each user there are 3 rows defined in ENDPOINT table e.g.

USER   ENDPOINT      ENDPOINTTYPE
123    123456        HomePhone
123    456789        CellPhone
123    [email protected]   Email

I want to display this data in a single row e.g.

USER   HomePhone   CellPhone   Email

Can you please tell me a way to achieve this?

Thanks a lot!

Upvotes: 0

Views: 333

Answers (2)

sgeddes
sgeddes

Reputation: 62851

Try something like this: 

SELECT 
    U.[User], 
    EP1.EndPoint as 'HomeEndpoint', EP1.Endpotintype as 'HomeEndpointType', 
    EP2.EndPoint as 'CellEndpoint', EP2.Endpotintype as 'CellEndpointType', 
    EP3.EndPoint as 'EmailEndpoint', EP3.Endpotintype as 'EmailEndpointType'
FROM Users U
    LEFT JOIN EndPoint EP1 ON U.[User] = EP1.[User] AND EP1.EndPointType = 'HomePhone'
    LEFT JOIN EndPoint EP2 ON U.[User] = EP2.[User] AND EP2.EndPointType = 'CellPhone'
    LEFT JOIN EndPoint EP3 ON U.[User] = EP3.[User] AND EP3.EndPointType = 'Email'

Or to return just those 4 columns:

SELECT 
    U.[User], 
    EP1.EndPoint as 'Home Phone', 
    EP2.EndPoint as 'Cell Phone', 
    EP3.EndPoint as 'Email'
FROM Users U
    LEFT JOIN EndPoint EP1 ON U.[User] = EP1.[User] AND EP1.EndPointType = 'HomePhone'
    LEFT JOIN EndPoint EP2 ON U.[User] = EP2.[User] AND EP2.EndPointType = 'CellPhone'
    LEFT JOIN EndPoint EP3 ON U.[User] = EP3.[User] AND EP3.EndPointType = 'Email'

Here is the SQL Fiddle -- Note, I changed column User to UserId since it's a reserved word (or you could have used brackets).

Upvotes: 2

Taryn
Taryn

Reputation: 247810

This type of data transformation is known as pivot. SQL Server has a pivot function starting with SQL Server 2005.

Prior to SQL Server 2005, this could be done using an aggregate function with a CASE expression:

select [user],
  max(case when ENDPOINTTYPE = 'HomePhone' then EndPoint end) HomePhone,
  max(case when ENDPOINTTYPE = 'CellPhone' then EndPoint end) CellPhone,
  max(case when ENDPOINTTYPE = 'Email' then EndPoint end) Email
from yourtable
group by [user];

See SQL Fiddle with Demo.

If you are using SQL Server 2005+, then the pivot query would look like:

select *
from
(
  select [user], endpoint, endpointtype
  from yourtable
) src
pivot
(
  max(endpoint)
  for endpointtype in (HomePhone, CellPhone, Email)
) piv

See SQL Fiddle with Demo

The above queries work great if you know the values ahead of time. If you don't then you will want to use dynamic sql:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ENDPOINTTYPE) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [USER],' + @cols + ' from 
             (
                select [USER], [ENDPOINT], [ENDPOINTTYPE]
                from yourtable
            ) x
            pivot 
            (
                max(ENDPOINT)
                for ENDPOINTTYPE in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

All queries return the same result:

| USER | HOMEPHONE | CELLPHONE |       EMAIL |
----------------------------------------------
|  123 |    123456 |    456789 | [email protected] |

Upvotes: 4

Related Questions