SQL Query - return row values as columns

Question

I have a table:

Part    X   Y   
ABC     1   10  
ABC     2   20  
ABC     3   30  
ABC     4   40  
ABC     5   x   
ABC     6   x   
XYZ     1   50  
XYZ     2   60  
XYZ     3   70  
XYZ     4   80  
XYZ     5   x   
XYZ     6   x   
ETC     1   90  
ETC     2   100 
ETC     3   110

Id like to select this data in the following way:

Part    1   2   3   4
ABC     10  20  30  40
XYZ     50  60  70  80
ETC     90  110 120 130

Is this possible?

Notes:

Header for results = 1,2,3,4 = Field x from the table
FieldX contains values 1 - 18, I'm only interested in 1-4
I'd later like to also Join this to a product table where I'll be able to specify the results based on the product.Supplier field.

I think Subqueries may be the way to go but not experienced in this area.

Thanks for any help offered

This is called pivoting and the solution depends on the database you are using. Eg SQL Server has the PIVOT/UNPIVOT commands, others use a combination of GROUP BY and conditionals. What database are you using? — Panagiotis Kanavos, May 19 '14 at 14:13
http://stackoverflow.com/questions/24470/sql-server-pivot-examples -- lots and lots of SO answers on this one... — sgeddes, May 19 '14 at 14:16
@Panagiotis Kanavos I'm using SQL Server 2008 - thanks for the Pivot suggestion. I'll investigate and mark the answer appropriately. — FrostyM8, May 19 '14 at 15:23

score 0 · Accepted Answer · answered May 19 '14 at 14:21

0

I'm only posting this as the previous answers seem way off. Without knowing your RDBMS, you can use MAX with CASE:

SElECT part,
    MAX(CASE WHEN X = 1 THEN Y END) Y1,
    MAX(CASE WHEN X = 2 THEN Y END) Y2,
    MAX(CASE WHEN X = 3 THEN Y END) Y3,
    MAX(CASE WHEN X = 4 THEN Y END) Y4
FROM YourTable
GROUP BY part

If you're RDBMS will support it, you can use the PIVOT command.

answered May 19 '14 at 14:21

sgeddes

62,311
6
61
83

Pivot may be the better way to achieve this but unfortunately not available on my database. I then used suggestion above and managed to get it working. Many thanks to all suggestions - marked as answer – FrostyM8 May 20 '14 at 08:04

SQL Query - return row values as columns

1 Answers1