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


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.


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

    (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.