I acquired an issue changing a table that appears such as this

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

into

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
  • considering that any city doesn't many 3 distributers

I've attempted the self join however i get duplicate records, please advise.

Tell me if the be completed in stand out ?? because this for confirming purpose only

You are able to PIVOT tables in SQL too.

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

I am unsure whether it's valid in Sybase SQL, still I'd begin with something similar to this in other SQL servers:

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

I believe this type of transformation is better completed in a study author, not within the database.