combine rows into one row in tsql

Question

let's say I have:

User     Email
 1       [email protected]
 1       [email protected]
 1       [email protected]
 2       [email protected]
 2       [email protected]
 3       [email protected]

and I want the output:

 User     Eamil1            Email2          Email3
 1        [email protected]     [email protected]   [email protected]
 2        [email protected]     [email protected]   
 3        [email protected]

I found other answers about using FOR XML Path, but that's not what I want. it combines rows into one column, and separate the values with ';'

I also tried JOIN the table with itself, and it's also not the result I want.

Any help is appreciated.

May I ask what is your intention for doing this?

Cory
– Cory

2014-10-14 21:54:35 +00:00
Commented Oct 14, 2014 at 21:54 — Cory
– Cory, Commented Oct 14, 2014 at 21:54

sqluser · Accepted Answer · 2014-10-15 01:06:02Z

2

Try something like this. Pivot is not a good solution in this case. Pivot is not efficient for non numeric values.

WITH C AS(
    SELECT 
    RANK() OVER (PARTITION BY [User] ORDER BY Email) AS [Rank]
    ,[User], Email
    FROM Emails
)
SELECT [User]
    ,MIN(CASE C.[Rank] WHEN 1 THEN Email END) AS [Email1]
    ,MIN(CASE C.[Rank] WHEN 2 THEN Email END) AS [Email2]
    ,MIN(CASE C.[Rank] WHEN 3 THEN Email END) AS [Email3]
FROM C
GROUP BY [User]

answered Oct 15, 2014 at 1:06

sqluser

5,7027 gold badges43 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Taryn Over a year ago

What do you mean pivot is not efficient for non numerical values?

triston Over a year ago

i will give this a try. i did some research on pivot. pivot mostly works well if you need to SUM/AVG int values on results. I haven't been able to get a way to make it work on email addresses yet.

Taryn Over a year ago

@triston You can use PIVOT on an an email address, you'll just need to use the aggregate function max or min on the column since it is a string.

gmiley · Accepted Answer · 2014-10-14 19:41:24Z

0

You should look into the PIVOT command.

http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php

answered Oct 14, 2014 at 19:41

gmiley

6,6041 gold badge15 silver badges25 bronze badges

Collectives™ on Stack Overflow

combine rows into one row in tsql

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related