Move from row to column

[egghi]

Happy Friday! I am having some hard time with a query I created...

My current query:
SELECT Code.h_codeid,Code.h_instance,Code.h_code,Code.h_date
FROM Code INNER JOIN Account ON
Account.SSAccountID=Code.SSAccountID AND
Account._counter=Code.claimlink
WHERE Code.h_codeid IN ('DI','PR') Code.h_instance BETWEEN 1 and 6 AND Account.SnapshotID IN
(SELECT MIN(Account.SnapshotID)
FROM Account)

I got results like:

h_codeid h_instance h_code h_date
DI 1 04111 Null
DI 2 03111 8/26/2006
PR 1 ZT333 8/26/2006
PR 2 ZT444 8/26/2006


However, I would like the results to look like:

DI1_Code DI1_Date DI2_Code DI2_Date PR1_Code PR1_Date
04111 Null 03111 8/26/2006 ZT333 8/26/2006

Hope you get the idea I will apprciate any advice!!

Thank you in advance,
egghi

 

[pjw001]

Use aliases to treat the code table as a number of different tables. In your example data it would be 2 tables but from your where clause I would guess that 6 is probably the number you want.

So your statement would be something like

FROM code d1, code d2, code d3 ...
WHERE d1.hinstance = 1
AND d2.h_instance = 2
etc