Query which converts rows into columns

Question

I have something like

ZoneNumber  AverageSpeed
      1             20
      2             30
      3             50
      4             60
      5             70

And I want to have a view like

AverageSpeed 1  AverageSpeed 2  AverageSpeed 3  AverageSpeed 4  AverageSpeed 5
         20                30              50              60              70

Does anyone have any ideas what my query need to be written like?

Which database system are you using? MySQL, PostgreSQL, MSSQL, Oracle, something else? What language is the target of your query? Do you want an array of objects returned, an array of tuples, and array of arrays or just a giant string, or do you really want an sql row back, with just the average speeds, 20, 30, 50, 60, etc? — so_, Apr 07 '15 at 21:49
possible duplicate of [Understanding PIVOT function in T-SQL](http://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql) — Nick.Mc, Aug 10 '15 at 04:15

score 1 · Answer 1 · answered Apr 07 '15 at 21:53

You need to use Pivot.

Pivot rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

Basic example:

SELECT *
FROM (
    SELECT year(invoiceDate) as [year], left(datename(month,invoicedate),3) as [month], InvoiceAmount as Amount 
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)
)AS pivot

Query which converts rows into columns

1 Answers1