1

I'm working on a table and need to get specific output with additional columns. In the first column of the table I have usernames and second column has email addresses. Users can have one or two email addresses. so column one will have duplicate values. I need return the data on the table using a SQL query with with three columns: username, first email address and second email address.

Could please assist with the query?

Example:

| username1 | email1@test.com |
| username1 | email2@test.com |

Output:

| username1 | email1@test.com | email2@test.com |
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
peter
  • 21
  • 2
  • Will there only ever be a maximum of 2 rows, is it limited to another number, or could it be "infinite"? – Thom A Feb 01 '19 at 11:58
  • 2
    Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Thom A Feb 01 '19 at 11:58

1 Answers1

0

Firstly welcome to Stackoverflow.

Assuming you are on at least SQL Server 2008, you can achieve this using row_number() and a self-join

To show how this works, I give a simple example:

declare @test table(username varchar(50), email  varchar(50))

insert INTO @test values('username1', 'email1@test.com')
insert INTO @test values('username1', 'email2@test.com')
insert INTO @test values('username2', 'email3@test.com')


;with cte as
(SELECT username, email, row_number() OVER (PARTITION BY username order by username) rn
from @test)
SELECT t1.username, t1.email as email1, t2.email as email2
FROM cte t1
LEFT JOIN cte t2 ON t1.username = t2.username AND t2.rn = 2
WHERE t1.rn = 1

By way of explanation, row_number() gives a unique number for each line, determined by the ORDER BY within the OVER. Adding PARTITION BY resets the row count for each new value specified by the PARTITION. In this case PARTITION BY and ORDER BY are the same field, but they need not be. Putting this all in a common table expression, then allows you to do a self-join (in this case an outer self-join) to pick up both those users with two emails (where rn will be 1 and 2) and those with just one. The left side of the join contains those with rn 1 (which will be all users), whilst the right side picks up the 2s.

Hope this helps

Jonathan

Jonathan Willcock
  • 5,012
  • 3
  • 20
  • 31