This week's book giveaway is in the Cloud forum.
We're giving away four copies of The Business Blockchain and have William Mougayar on-line!
See this thread for details.
Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL data convert column to row values

 
sudhir pavan
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,


I have a table with only one column about 100 rows of only names. But I need to display the 3 names in a row. So that I will get 34 rows each row with 3 names with order by primary key.

Name ID
_____ ___

Raj 1
Sam 2
Guru 3
Tej 4
Avin 5
Sami 6
Fanst 7

I need to display data as below

Name Name1 Name2
____ _____ ______
Raj Sam Guru
Tej Avin Sami
Fanst

No condition just need to covert single column value into 3 columns data. But only thing is need to order by primary key value.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This operation perhaps could be done in SQL, but the SQL query would be rather cumbersome and perhaps slow. This is a typical procedural operation, while SQL is designed to work over sets. Also, if you really needed to create such a query, you might end up with database dependent (ie. non-portable) code.

If you only need the query to read the resulting recordset for display, a much better approach would be to simply select the records in the order you need and arrange them to tabular form as they come from the database.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What database are you using?

This might give some inspiration if you are using SQL Server.
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:What database are you using?

This might give some inspiration if you are using SQL Server.


The problem there is that those are all essentially pivots, and the requirements here are not a pivot but an essentially arbitrary restructuring of the data.
There's no column that you're pivoting on, unless you construct one based on "id / 3".

I think Martin's correct and this is not really something that SQL should be handling (apart from actually getting hold of the data).
 
amit punekar
Ranch Hand
Posts: 544
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,
IMO, this is just a display requirement.
Why can't you just query the database with order by Primary key and then display whichever way you like (JSP or whatever you are using at your end? )

Regards,
Amit
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:I think Martin's correct and this is not really something that SQL should be handling (apart from actually getting hold of the data).

I agree! But sometimes I really enjoy a little bit of SQL fun Assuming you are using MySql I developed a query which fits your needs.

You have your script to create your table and populate with some test data.

And then you can execute this query.

Output:
As you can see, it's no problem if there are gaps between the ids of the records.

Here you'll find the SQLFiddle demo of these scripts.

Hope it helps!
Kind regards,
Roel
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic