I have a Sql Database table similar to the following:
我有一个类似于以下的Sql数据库表:
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
I would like it displayed as follows: Kind of crosstab or Pivot
我希望它显示如下:一种交叉表或枢轴。
Day P1 P2 P3 P4 P5 P6 P7
Mon Ch Ph Mth CS2 Lab1 Lab2 Lab3
Tue Ph Ele Hu Ph En CS2 Mth
What would be the ideal way to do it? Can someone please show me the Sql code please?
理想的方法是什么?能给我看看Sql代码吗?
6 个解决方案
#1
17
You could probably do it with the PIVOT function, but I prefer the old school method:
你可以用主函数来做,但我更喜欢老式的方法
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
- I changed some column names to avoid reserved words
- 我更改了一些列名以避免保留词
#2
12
Just incase you do want the new school method. (The Pivot statement should work in SQL2005+, the VALUES
bit for the example data only SQL2008)
以防你想要新的学校教育方法。(Pivot语句应该在SQL2005+中使用,而示例数据的值位仅在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;
Result
结果
Day P1 P2 P3 P4 P5 P6 P7
---- ---- ---- ---- ---- ---- ---- ----
Mon Ch Ph Mth CS Lab1 Lab2 Lab3
Tue Ph Ele Hu Ph En CS2 Mth
#3
1
You could try...
你可以试试…
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;
but I can't say I like it very much. Better than nothing, though.
但我不能说我很喜欢它。不过,总比没有好。
#4
1
Use cross apply to get all the values in a comma delimted format in a single column. instead of "7" different columns. The following query can be used for any column-> row mapping
使用cross apply在一列中以逗号分隔格式获取所有值。而不是“7”不同的列。以下查询可用于任何列——>行映射
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)
You will get [Ch,Ph,Mth,CS2,Lab1,Lab2,Lab3] in one column for Mon and so on ... You could use this as a table to query for any particular Day.
你会得到[Ch,Ph,Mth,CS2,Lab1,Lab2,Lab3]在Mon的一个列中,等等……您可以将其用作查询任何特定日期的表。
Hope this helps
希望这有助于
#5
1
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]
#6
0
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;
#1
17
You could probably do it with the PIVOT function, but I prefer the old school method:
你可以用主函数来做,但我更喜欢老式的方法
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
- I changed some column names to avoid reserved words
- 我更改了一些列名以避免保留词
#2
12
Just incase you do want the new school method. (The Pivot statement should work in SQL2005+, the VALUES
bit for the example data only SQL2008)
以防你想要新的学校教育方法。(Pivot语句应该在SQL2005+中使用,而示例数据的值位仅在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;
Result
结果
Day P1 P2 P3 P4 P5 P6 P7
---- ---- ---- ---- ---- ---- ---- ----
Mon Ch Ph Mth CS Lab1 Lab2 Lab3
Tue Ph Ele Hu Ph En CS2 Mth
#3
1
You could try...
你可以试试…
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;
but I can't say I like it very much. Better than nothing, though.
但我不能说我很喜欢它。不过,总比没有好。
#4
1
Use cross apply to get all the values in a comma delimted format in a single column. instead of "7" different columns. The following query can be used for any column-> row mapping
使用cross apply在一列中以逗号分隔格式获取所有值。而不是“7”不同的列。以下查询可用于任何列——>行映射
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)
You will get [Ch,Ph,Mth,CS2,Lab1,Lab2,Lab3] in one column for Mon and so on ... You could use this as a table to query for any particular Day.
你会得到[Ch,Ph,Mth,CS2,Lab1,Lab2,Lab3]在Mon的一个列中,等等……您可以将其用作查询任何特定日期的表。
Hope this helps
希望这有助于
#5
1
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]
#6
0
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;