PIVOT, Row_Number() returning multiple rows

Question

I'm trying to pivot a list of emails onto a single row, but for some reason my primary email address is getting a row of its own, so Im ending up with two rows per contact.

CREATE TABLE #tmp (
ID VARCHAR(20)
, EMAIL VARCHAR(50)
, isPrimary VARCHAR(1)
)

INSERT INTO #tmp (ID, EMAIL, isPrimary) VALUES ('B4100358183Q!ZH.ETES', '[email protected]', '1')
INSERT INTO #tmp (ID, EMAIL, isPrimary) VALUES ('B4100358183Q!ZH.ETES', '[email protected]', '0')
INSERT INTO #tmp (ID, EMAIL, isPrimary) VALUES ('B4100358183Q!ZH.ETES', '[email protected]', '0')

How would I amend the script below to return only one row containing all 3 email addresses rather than two rows?

SELECT ID, [1], [2], [3]
FROM (
  Select ID, EMAIL, isPrimary 
  , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY isPrimary desc) AS rn
  from #tmp
) eml
PIVOT (
  min(EMAIL)
  FOR rn IN ([1],[2],[3])
) P

Any help would be appreciated, thanks.

Scott Hodgin - Retired · Accepted Answer · 2018-04-20 11:36:24Z

5

Try removing isPrimary from your SELECT where you're calculating the ROW_NUMBER().

SELECT ID, [1], [2], [3]
FROM (
  Select ID, EMAIL
  , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY isPrimary desc) AS rn
  from #tmp
) eml
PIVOT (
  min(EMAIL)
  FOR rn IN ([1],[2],[3])
) P

| ID                   | 1                  | 2                   | 3            |
|----------------------|--------------------|---------------------|--------------|
| B4100358183Q!ZH.ETES | [email protected] | [email protected] | [email protected] |

answered Apr 20, 2018 at 11:36

Scott Hodgin - Retired

24.1k2 gold badges29 silver badges53 bronze badges

1

Aaah, I understand now. That does indeed resolve the issue. Thanks for you speedy response.

Don
– Don

2018-04-20 12:22:10 +00:00
Commented Apr 20, 2018 at 12:22

Add a comment |

Stack Exchange Network

PIVOT, Row_Number() returning multiple rows

1 Answer 1

Your Answer

Hot Network Questions

PIVOT, Row_Number() returning multiple rows

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions