How to pivot string rows to multiple columns?

Question

I have searched and tried and have not found the exact SQL code example I am looking for. I have multiple reps per an account (in rows) and need to spread these out into columns. I will have up to 3 for each account.

My source data looks like this:

+---------+-------+------+
| Account | Badge | Name |
+---------+-------+------+
|     123 |   456 | Bob  |
|     123 |   789 | John |
|     123 |   654 | Carl |
+---------+-------+------+

I need it to look like this:

+---------+--------+-------+--------+-------+--------+-------+
| Account | Badge1 | Name1 | Badge2 | Name2 | Badge3 | Name3 |
+---------+--------+-------+--------+-------+--------+-------+
|     123 |    456 | Bob   |    789 | John  |    654 | Carl  |
+---------+--------+-------+--------+-------+--------+-------+

Any help is greatly appreciated.

Which dbms are you using?

jarlh
– jarlh

2018-09-04 20:52:17 +00:00
Commented Sep 4, 2018 at 20:52 — jarlh
– jarlh, Commented Sep 4, 2018 at 20:52
SQL Server 2017

Gordy
– Gordy

2018-09-04 21:39:54 +00:00
Commented Sep 4, 2018 at 21:39 — Gordy
– Gordy, Commented Sep 4, 2018 at 21:39

D-Shih · Accepted Answer · 2018-09-04 21:51:54Z

3

You can try this.

If you DBMS support ROW_NUMBER and window function, make row number in a subquery, then do condition aggregate function make pivot

CREATE TABLE T(
   Account INT,
   Badge INT,
   Name VARCHAR(50)

);


INSERT INTO T VALUES (123,456,'Bob');
INSERT INTO T VALUES (123,789,'John');
INSERT INTO T VALUES (123,654,'Carl');

Query 1:

SELECT Account,
      MAX(CASE WHEN rn = 1 THEN Badge end) Badge1,
      MAX(CASE WHEN rn = 1 THEN Name end) Name1,
      MAX(CASE WHEN rn = 2 THEN Badge end) Badge2,
      MAX(CASE WHEN rn = 2 THEN Name end) Name2,
      MAX(CASE WHEN rn = 3 THEN Badge end) Badge3,
      MAX(CASE WHEN rn = 3 THEN Name end) Name3
FROM 
(
    SELECT  *,ROW_NUMBER() OVER(ORDER BY Account) rn
    FROM T
) t1
GROUP BY Account

Results:

| Account | Badge1 | Name1 | Badge2 | Name2 | Badge3 | Name3 |
|---------|--------|-------|--------|-------|--------|-------|
|     123 |    456 |   Bob |    789 |  John |    654 |  Carl |

edited Sep 4, 2018 at 21:51

answered Sep 4, 2018 at 20:54

D-Shih

46.5k6 gold badges38 silver badges56 bronze badges

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

3 Comments

Gordy Over a year ago

I get Account Badge1 Name1 Badge2 Name2 Badge3 Name3 123 456 Bob 789 John 654 Carl 968 NULL NULL NULL NULL NULL NULL For the rest of the account numbers, the query returns NULL values.

D-Shih Over a year ago

@Gordy It work in sqlfiddle sqlfiddle.com/#!18/c5568/1 or you need to provide more sapmle data from you table

D-Shih Over a year ago

sqlfiddle.com/#!18/c5568/2 you can add WHERE Account IS NOT NULL

Collectives™ on Stack Overflow

How to pivot string rows to multiple columns?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related