Member Avatar for gennesis

Hi guys. I have a table as follows:

ID	|	Data
----------------------
1	|	A
1	|	B
1	|	C

It is possible to make like this one.

ID	|	Data1	|	Data2	|	Data3
-------------------------------------------------------
1	|	A	|	B	|	C

Thanks.

  • 4 Contributors
  • 3 Replies
  • 125 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by drjohn
Member Avatar for smantscheff

Not in a single query with general validity.
You could write a procedure which generates a CREATE TABLE statement with one column for each row of the original table and then execute this statement in a stored procedure. Or you could do it with a script language like PHP. But there is no query which returns columns for rows.
For this special case, you might query:

drop table if exists mytable;
create table mytable (id integer, data char);
insert into mytable values (1,'A'),(1,'B'),(1,'C');
select d1.ID, d1.Data as Data1, d2.Data as Data2, d3.Data as Data3
from mytable d1, mytable d2, mytable d3
where d1.ID=d2.ID and d2.ID=d3.ID
and d1.Data < d2.Data and d2.Data < d3.data ;
Member Avatar for kartisathis

hi Mr.smantscheff
If am having data like this

id 	data
1 	A
1 	B
1 	C
2 	A
2 	B

and i need a solution like this ,

ID	|	Data1	|	Data2	|	Data3
-------------------------------------------------------
1	|	A	|	B	|	C
2       |       A       |       B       |       -
Member Avatar for drjohn

I suspect you are trying to break your normalised table, and if you store data in the format you are suggesting, querying the table would become trickier. In your example above, all the As are in data1, all the Bs in data2, all the Cs in data3. But somehow I don't think you can absolutely guarentee that. So if the A for id 27 and several others was in data2, and the A for id 125 and several others was in data2 while for id 250 the a was in data2, you would have to write a query that inspected every column to find all the rows containing an A.

This style of data storage is not recommended.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.