Transform a table horizontally, with filling NULL columns when needed

I have two tables
ORDERS

_____________________
|OrdNum     |Stockid |
----------------------
|1234       |alpha   |  
|1238       |beta    | 
|1745       |gamma   |  
----------------------

and MARKS

______________________________
|OrdNum     |RowNum|   Mark  |
------------------------------
|1234       |1     |   AB    | 
|1238       |1     |   XY    |    
|1238       |2     |   XZ    |   
|1745       |1     |   KD    |    
|1745       |2     |   KS    |    
|1745       |3     |   JJ    |     
|1745       |4     |   RT    |     
|1745       |5     |   PJ    |   
------------------------------

For every order, there can be from 1 up to a maximum of 5 corresponding records in the MARKS table.
I need to do a sort of "Horizontal" join, with NULL values where no corrispondance occurs. Frankly speaking, I am not even sure it can be done. The final results should look like the following

_____________________________________________________
|OrdNum     |Mark1  |Mark2  |Mark3  |Mark4  |Mark5  |
-----------------------------------------------------
|1234       |AB     |NULL   |NULL   |NULL   |NULL   | 
|1238       |XY     |XZ     |NULL   |NULL   |NULL   |    
|1238       |KD     |KS     |JJ     |RT     |PJ     |   
-----------------------------------------------------

Again, the final view has to be horizontal, with 5 (plus order number) columns. Anybody know if this is possible ? Thank you in advance.

---

In T-SQL (SQL Server 2008) I did try the following joing For the sake of simplification, let us consider only 2 columns instead of 5.

SELECT o.OrdNum, 
m1.Mark AS MARK1,
m2.Mark AS MARK2,
FROM ORDERS o 
LEFT JOIN MARKS m1 ON o.OrdNum = m1.OrdNum WHERE m1.RowNum=1
LEFT JOIN MARKS m2 ON o.OrdNum = m2.OrdNum WHERE m2.RowNum=2

which is synthetically wrong.
So I modified the join as it follows

SELECT o.OrdNum, 
m1.Mark AS MARK1,
m2.Mark AS MARK2,
FROM ORDERS o 
LEFT JOIN MARKS m1 ON o.OrdNum = m1.OrdNum 
LEFT JOIN MARKS m2 ON o.OrdNum = m2.OrdNum 
WHERE m1.RowNum=1 AND m2.RowNum=2

which is not what I want to get, since it does not produce the orders having only the record corresponding to the first RowNum (with the values at the beginning of this post, it will not show order number 1234 ..)

链接地址: http://www.djcxy.com/p/76298.html

上一篇: Postgres和索引外键和主键

下一篇: 水平变换表格，需要时填充NULL列