Pivoting some row data into columns (SQL Server 2008 R2)

Question

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! [email protected]
CD# 1A! [email protected]

I need to select the data like so:

Dumb_ID Col1 Col2 Col3
1A! James Q. [email protected] [email protected]

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 Dumb_ID

Please help DBA's, you're my only hope to escape the madness of this application.

Thanks!

EDIT:
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

Answer 1

Ok, here's my two pence:

SELECT header.Col1,
 MAX((CASE WHEN details.ordinal=1 THEN details.Col2 END)) AS row1,
 MAX((CASE WHEN details.ordinal=2 THEN details.Col2 END)) AS row2,
 -- ... and so on..
 MAX((CASE WHEN details.ordinal=99 THEN details.Col2 END)) AS row99
FROM (
 --- For each Col1, enumerate all the rows and return Col2 as well:
 SELECT Col1, Col2,
 ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Dumb_ID) AS ordinal
 FROM someTable
 ) AS details
--- join this to the header:
INNER JOIN someTable AS header ON details.Col1=header.Dumb_ID
--- One row per header:
GROUP BY header.Col1;

Admittedly, not the prettiest code I've ever written. How it works:

• The "header" in this case is the row where Dumb_ID='1A!'.
• "details" contains one row for each record that can be joined to the header ON header.Dumb_ID=details.Col1, in this example rows where Col1='1A!'.
• The "details" subquery is given an ordinal, a ROW_NUMBER(), that enumerates each row (partitioned by Col1).
• "header" and "details" are joined and everything is output by aggregating the result, so we get a single record for each "header"
• Each "detail" is assigned to its own column using a CASE and the ordinal number. We need to use MAX() (or any other aggregate function, really) here so it'll work with the aggregate.

I hope I've understood your question correctly. Also, note that this is ad-hoc coded, without testing. Let me know how it works out.

• James: Augh, That was perfect, I completely forgot about PARTITION.
• Daniel Hutmacher: @James, I've revised the answer because I didn't properly read your question. I hope I got it right.
• James: First answer got it for me, excellent answer Daniel. I'd give you an upvote as well but I don't have enough reputation. Thanks Again!