How to transform vertical data into horizontal data with SQL?

by

Regardless of the database you are using, the concept of what you are trying to achieve is called “Pivot Table”.

Here’s an example for mysql:
http://en.wikibooks.org/wiki/MySQL/Pivot_table

Some databases have builtin features for that, see the links below.

SQLServer:
http://msdn.microsoft.com/de-de/library/ms177410.aspx

Oracle:
http://www.dba-oracle.com/t_pivot_examples.htm

You can always create a pivot by hand. Just select all the aggregations in a result set and then select from that result set. Note, in your case, you can put all the names into one column using concat (i think that’s group_concat in mysql), since you cannot know how many names are related to a a rel_id.

pseudo-select for your case (i don’t know mysql):

select rel_id, group_concat(name) from item group by rel_id

More Related Contents:

  1. SQL – How to transpose?
  2. How can I return pivot table output in MySQL?
  3. SQL Server dynamic PIVOT query?
  4. Join vs. sub-query
  5. Natural Sort in MySQL
  6. MySQL get row position in ORDER BY
  7. Subqueries vs joins
  8. MySQL Results as comma separated list
  9. MySQL unknown column in ON clause
  10. SQL Server 2005 Pivot on Unknown Number of Columns
  11. MySQL not using indexes with WHERE IN clause?
  12. SQL: Find the max record per group [duplicate]
  13. Group by month and year in MySQL
  14. calculate a sum of type time using sql
  15. Remove duplicates using only a MySQL query?
  16. Using pivot on multiple columns of an Oracle row
  17. How exactly does using OR in a MySQL statement differ with/without parentheses?
  18. PIVOT query on Distinct records
  19. MySQL: How to copy rows, but change a few fields?
  20. How to renumber primary index
  21. Query that ignore the spaces
  22. mysql automatically store record creation timestamp
  23. SQL Transpose Rows as Columns
  24. Pivoting in DB2
  25. update columns values with column of another table based on condition [duplicate]
  26. MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) – Unexpected result
  27. Flattening of a 1 row table into a key-value pair table
  28. MySql bulk load command line tool
  29. ALTER TABLE without locking the table?
  30. MySQL: Typecasting NULL to 0

Leave a Comment