Convert columns into rows with PIVOT

Question

Convert columns into rows with PIVOT

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have the following query:

SELECT COUNT(*) 'Impartidas', DAY(kh.Hora) AS 'Dia'
FROM K_Horario kh
WHERE CONVERT (char(10),kh.Hora,20) BETWEEN '2017-07-03' AND '2017-07-09'
GROUP BY DAY(kh.Hora) ORDER BY DAY(kh.Hora)

And it returns the following results:

+------------+-----+
| Impartidas | Dia |
+------------+-----+
|         31 |   3 |
|         28 |   4 |
|         29 |   5 |
|         27 |   6 |
|         24 |   7 |
|          7 |   8 |
|          7 |   9 |
+------------+-----+

My goal is for the days to send them in a row so I do the following:

SELECT 'ClasesPorDia' AS Dia, [3],[4],[5],[6],[7],[8],[9]
FROM (SELECT DAY(kh.Hora)FROM K_Horario kh WHERE CONVERT(char(10),kh.Hora,20) BETWEEN '2017-07-03' AND '2017-07-09')
PIVOT (COUNT(*) FOR DAY(kh.Hora) IN ([3],[4],[5],[6],[7],[8],[9]))

sql-server pivot

asked by Guillermo Ricardo Spindola Bri 15.08.2017 в 21:09

source

2 answers

0

I found the answer by adding an alias after the SELECT and another after the PIVOT, leaving as follows:

    SELECT 'ClasesPorDia' AS Dia, [3],[4],[5],[6],[7],[8],[9]
FROM (SELECT OIDHorario, DAY(Hora) as DiaClase FROM K_Horario WHERE 
CONVERT(char(10),Hora,20) BETWEEN '2017-07-03' AND '2017-07-09') AS V
PIVOT (COUNT(OIDHorario) FOR DiaClase IN ([3],[4],[5],[6],[7],[8],[9])) AS PTS

answered by 15.08.2017 в 23:55

sort from largest to smallest array of objects extracted from firebase with angularfire2 System.InvalidOperationException: An entity object can not be referenced by multiple instances of IEntityChangeTracker

score 1 · Accepted Answer

If you have a primary key in your K_Horario table, this query will serve you, replace Primary Key in the two places that I put it with your own primary key:

SELECT 'ClasesPorDia' AS Dia, [3],[4],[5],[6],[7],[8],[9]
FROM (SELECT LlavePrimaria, DAY(kh.Hora) as DiaClase FROM K_Horario kh WHERE 
CONVERT(char(10),kh.Hora,20) BETWEEN '2017-07-03' AND '2017-07-09') as TablaDias
PIVOT (COUNT(LlavePrimaria) FOR DiaClase IN ([3],[4],[5],[6],[7],[8],[9])) as PivotDias

Greetings,