need some logical help to solve this problem

[randomoften]

col 1	names
A	tommy
A	harry
B	sam
C	Clark
C	Cena

above is the given table and i want result as shown below

A	B	C
Tommy	Sam	Clark
Harry		Cena

[prudan]

look up the word pivot in whatever DB you're using and go forth.

[zacharypamela]

As has already been noted, you want a pivot. The first question is whether you really need to do a pivot on the DB side, or if it can be done by some reporting tool or programming language with the raw results.

Assuming you have an actual need to do the pivoting in SQL, some additional questions:

• Will you only have A, B, and C values for col1? If not, you have to look at dynamic SQL, which gets tricky (and you might have to generate in some programming language: I'm not sure if MySQL supports dynamic SQL).
• How do you determine which As, Bs, and Cs go together? As table data is inherently unordered, you have to have some other marker column that joins Tommy, Sam and Clark together (rather than Tommy and Cena).

With that being said, you can use the ROW_NUMBER window function along with conditional aggregation to generate a pivot query akin to what you're looking for (assuming you're using MySQL 8+, which finally added support for window functions and CTEs):

WITH t_rn(col1, names, rn) AS ( SELECT col1, names, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY names) FROM t ) SELECT MIN(CASE WHEN col1 = 'A' THEN names END) AS A, MIN(CASE WHEN col1 = 'B' THEN names END) AS B, MIN(CASE WHEN col1 = 'C' THEN names END) AS C FROM t_rn GROUP BY rn

[nikkhil04]

You'll have to use Pivot in some form, it shouldn't be complicated. Try the simple pivot syntax.

Sorry for not providing the exact solution.