0

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', 'ABoyd@Energize.com', '1')
INSERT INTO #tmp (ID, EMAIL, isPrimary) VALUES ('B4100358183Q!ZH.ETES', 'BSykes@Energize.com', '0')
INSERT INTO #tmp (ID, EMAIL, isPrimary) VALUES ('B4100358183Q!ZH.ETES', 'TC6@test.com', '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.

3

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 | ABoyd@Energize.com | BSykes@Energize.com | TC6@test.com |
  • Aaah, I understand now. That does indeed resolve the issue. Thanks for you speedy response. – Don Apr 20 '18 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.