Sorry if this has been answered before but I'm exhausted and on a time line. Maybe I'm just an idiot. I'm designing a data export from One application to import into another.
Full-Disclosure: I did not design this disaster of an application
We have an application that stores email addresses and contacts on different rows in the same table. The problem is that the primary email for that user is simply the one that comes back first when the rows are sorted by a unique id field.
Dumb_ID Col1 Col2 1A! James Q. 2B$ 1A! JamesQ@Contoso.com CD# 1A! JamesQ@OldButRelevantContoso.Ru
I need to select the data like so:
Dumb_ID Col1 Col2 Col3 1A! James Q. JamesQ@Contoso.com JamesQ@OldButRelevantContoso.Ru
There are a number of other columns being selected from 1A! but I cant think of an effective way to select these into a column based on their row order when sorted by
Please help DBA's, you're my only hope to escape the madness of this application.
After @Daniel gave me an elegant method using a subquery with a partitioned row count this was my end result. Posted for posterity.
LEFT OUTER JOIN (SELECT LINKACCT , ADDRESS2 ,MAX(CASE WHEN subQ.ORDINAL = 1 THEN (subQ.CONTSUPREF + subQ.ADDRESS1) END) AS EMAIL1 ,MAX(CASE WHEN subQ.ORDINAL = 2 THEN (subQ.CONTSUPREF + subQ.ADDRESS1) END) AS EMAIL2 ,MAX(CASE WHEN subQ.ORDINAL = 3 THEN (subQ.CONTSUPREF + subQ.ADDRESS1) END) AS EMAIL3 FROM (SELECT recid , CONTACT , CONTSUPREF , LINKACCT , ADDRESS1 , ADDRESS2 ,ROW_NUMBER() OVER (PARTITION BY LINKACCT ORDER BY recid) AS ORDINAL FROM CONTSUPP CSEI WHERE CSEI.RECTYPE = 'P') subQ GROUP BY LINKACCT, ADDRESS2) CSEI ON CS.recid = CSEI.LINKACCT AND CS.CONTACT = CSEI.ADDRESS2