Abbaskhan Abbaskhan - 1 month ago 6
SQL Question

SQL pivot query data

Can someone show me the query. I have a table with the below data which I wish to pivot.

Name Ride Hour
Abbas Falls 1
Abbas Falls 2
Abbas Falls 3
Shiraz Falls 1
Shiraz Falls 2
Shiraz Falls 3
Adnan Falls 1
Adnan Falls 2
Adnan Falls 3
Adnan Falls 4
Abbas Lunch 4
Shiraz Lunch 4
Adnan Lunch 5
Abbas Jolly 5
Shiraz Jolly 5
Abbas Jolly 6
Shiraz Jolly 6
Adnan Jolly 6


I want to convert it to look like this:

Name 1 2 3 4 5 6
Abbas falls falls falls Lunch Jolly Jolly
Shiraz falls falls falls Lunch Jolly Jolly
Adnan falls falls falls falls Lunch Jolly


I wrote this but it doesn't do the job

SELECT *
FROM
(SELECT *
FROM _Test) as test
PIVOT
(
COUNT(ride)
FOR hour IN ([1], [2], [3], [4], [5], [6])
) AS PivotTable

Answer

Use MAX instead of COUNT:

 SELECT *
 FROM (SELECT *
       FROM Test) as test
 PIVOT (
   MAX(ride)
   FOR hour IN ([1], [2], [3], [4], [5], [6])) AS PivotTable
Comments