Cómo pivotar filas en columnas (pivotar personalizado)
Tengo una tabla de base de datos Sql similar a la siguiente:
Day Period Subject
Mon 1 Ch
Mon 2 Ph
Mon 3 Mth
Mon 4 CS
Mon 5 Lab1
Mon 6 Lab2
Mon 7 Lab3
Tue 1 Ph
Tue 2 Ele
Tue 3 Hu
Tue 4 Ph
Tue 5 En
Tue 6 CS2
Tue 7 Mth
Me gustaría que se muestre de la siguiente manera: Tipo de tabla cruzada o Pivote
Day P1 P2 P3 P4 P5 P6 P7
Mon Ch Ph Mth CS2 Lab1 Lab2 Lab3
Tue Ph Ele Hu Ph En CS2 Mth
¿Cuál sería la forma ideal de hacerlo? ¿Puede alguien mostrarme el código Sql, por favor?
6 answers
Probablemente podrías hacerlo con la función PIVOT, pero prefiero el método de la vieja escuela:
SELECT
dy,
MAX(CASE WHEN period = 1 THEN subj ELSE NULL END) AS P1,
MAX(CASE WHEN period = 2 THEN subj ELSE NULL END) AS P2,
MAX(CASE WHEN period = 3 THEN subj ELSE NULL END) AS P3,
MAX(CASE WHEN period = 4 THEN subj ELSE NULL END) AS P4,
MAX(CASE WHEN period = 5 THEN subj ELSE NULL END) AS P5,
MAX(CASE WHEN period = 6 THEN subj ELSE NULL END) AS P6,
MAX(CASE WHEN period = 7 THEN subj ELSE NULL END) AS P7
FROM
Classes
GROUP BY
dy
ORDER BY
CASE dy
WHEN 'Mon' THEN 1
WHEN 'Tue' THEN 2
WHEN 'Wed' THEN 3
WHEN 'Thu' THEN 4
WHEN 'Fri' THEN 5
WHEN 'Sat' THEN 6
WHEN 'Sun' THEN 7
ELSE 8
END
- Cambié algunos nombres de columna para evitar palabras reservadas
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-06-25 19:41:03
Solo en caso de que quieras el nuevo método escolar. (La instrucción Pivot debería funcionar en SQL2005+, el bit VALUES
solo para los datos de ejemplo SQL2008)
WITH ExampleData AS
(
SELECT X.*
FROM (VALUES
('Mon', 1, 'Ch'),
('Mon', 2, 'Ph'),
('Mon', 3, 'Mth'),
('Mon', 4, 'CS'),
('Mon', 5, 'Lab1'),
('Mon', 6, 'Lab2'),
('Mon', 7, 'Lab3'),
('Tue', 1, 'Ph'),
('Tue', 2, 'Ele'),
('Tue', 3, 'Hu'),
('Tue', 4, 'Ph'),
('Tue', 5, 'En'),
('Tue', 6, 'CS2'),
('Tue', 7, 'Mth')
) AS X (Day, Period, Subject)
)
SELECT Day, [1] AS P1, [2] AS P2,[3] AS P3, [4] AS P4, [5] AS P5,[6] AS P6,[7] AS P7
FROM ExampleData
PIVOT
(
Max(Subject)
FOR Period IN ([1], [2],[3],[4], [5],[6], [7])
) AS PivotTable;
Resultado
Day P1 P2 P3 P4 P5 P6 P7
---- ---- ---- ---- ---- ---- ---- ----
Mon Ch Ph Mth CS Lab1 Lab2 Lab3
Tue Ph Ele Hu Ph En CS2 Mth
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-06-25 19:50:02
Podrías intentarlo...
SELECT DISTINCT Day,
(SELECT Subject
FROM my_table mt2
WHERE mt2.Day = mt.Day AND
Period = 1) AS P1,
(SELECT Subject
FROM my_table mt2
WHERE mt2.Day = mt.Day AND
Period = 2) AS P2,
.
.
etc
.
.
.
(SELECT Subject
FROM my_table mt2
WHERE mt2.Day = mt.Day AND
Period = 7) AS P7
FROM my_table mt;
Pero no puedo decir que me guste mucho. Mejor que nada, sin embargo.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-06-25 19:40:41
Use cross apply para obtener todos los valores en un formato delimitado por comas en una sola columna. en lugar de "7" columnas diferentes. La siguiente consulta se puede utilizar para cualquier asignación de columnas-> filas
SELECT DISTINCT Day, [DerivedColumn] FROM <Table> A CROSS APPLY ( SELECT Period + ',' FROM <Table> B WHERE A.Day = B.Day Order By Period FOR XML PATH('') ) AS C (DerivedColumn)
Obtendrá [Ch,Ph,Mth,CS2,Lab1,Lab2,Lab3] en una columna para Mon y así sucesivamente ... Puede usar esto como una tabla para consultar cualquier día en particular.
Espero que esto ayude
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-06-25 19:48:24
DECLARE @TIMETABLE TABLE (
[Day] CHAR(3),
[Period] TINYINT,
[Subject] CHAR(5)
)
INSERT INTO @TIMETABLE([Day], [Period], [Subject])
VALUES
('Mon', 1, 'Ch'),
('Mon', 2, 'Ph'),
('Mon', 3, 'Mth'),
('Mon', 4, 'CS'),
('Mon', 5, 'Lab1'),
('Mon', 6, 'Lab2'),
('Mon', 7, 'Lab3'),
('Tue', 1, 'Ph'),
('Tue', 2, 'Ele'),
('Tue', 3, 'Hu'),
('Tue', 4, 'Ph'),
('Tue', 5, 'En'),
('Tue', 6, 'CS2'),
('Tue', 7, 'Mth')
SELECT
[Day],
MAX(CASE [Period] WHEN 1 THEN [Subject] END) AS P1,
MAX(CASE [Period] WHEN 2 THEN [Subject] END) AS P2,
MAX(CASE [Period] WHEN 3 THEN [Subject] END) AS P3,
MAX(CASE [Period] WHEN 4 THEN [Subject] END) AS P4,
MAX(CASE [Period] WHEN 5 THEN [Subject] END) AS P5,
MAX(CASE [Period] WHEN 6 THEN [Subject] END) AS P6,
MAX(CASE [Period] WHEN 7 THEN [Subject] END) AS P7
FROM @TIMETABLE
GROUP BY [Day]
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-01-20 08:19:29
with pivot_data as
(
select [day], -- groping column
period, -- spreading column
subject -- aggreate column
from pivot_tb
)
select [day], [1] AS P1, [2] AS P2,[3] AS P3, [4] AS P4, [5] AS P5,[6] AS P6,[7] AS P7
from pivot_data
pivot ( max(subject) for period in ([1], [2],[3],[4], [5],[6], [7]) ) as p;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-12-20 07:07:01