SQL查询转换表

Question

我在将一个看起来像这样的表转换

city    | distributor | phno
---------------------------------
new york   xxx           12345

new york   yyy           12312

new york   zzz           12313

london     aaa           12315

london     bbb           11111

hong konk  ccc           12311

为

city    | distributor1 | phno1 | distributor2 | phno2 | distributor3 | phno3
-----------------------------------------------------------------------------
new york   xxx           12345      yyy         12312     zzz           12313

london     aaa           12315      bbb         11111     0             0             

hong konk  ccc           12311       0            0       0             0

• 考虑到任何城市都没有更多的表和 3 个分销商

时遇到问题，我已经尝试过自连接，但我得到了重复的记录，请告知。

请告诉我这是否在 Excel 中完成？因为这仅用于报告目的

Answer 1

您也可以在 SQL 中对表进行数据透视。

http://msdn.microsoft.com/en-us/library/ms177410.aspx

Answer 2

我认为这种转换最好在报告编写器中完成，而不是在数据库中完成。

Answer 3

我不确定它在 Sybase SQL 中是否有效，但我仍然会在其他 SQL 服务器中从类似的内容开始：

SELECT * FROM 
    (SELECT city FROM table GROUP BY city) AS c
LEFT JOIN (SELECT TOP 1 distributor AS distributor1, phno AS phno1 
    FROM table c1 WHERE c1.city = c.city 
    ORDER BY distributor ASC) AS c1
    ON true
LEFT JOIN (SELECT TOP 2 distributor AS distributor2, phno AS phno2 
    FROM table c2 WHERE c2.city = c.city 
    ORDER BY distributor ASC) AS c2
    ON distributor1 != distributor2
LEFT JOIN (SELECT TOP 3 distributor AS distributor3, phno AS phno3 
    FROM table c3 WHERE c3.city = c.city 
    ORDER BY distributor ASC) AS c3
    ON distributor1 != distributor3 AND distributor2 != distributor3