SQL 如何将行透视为列（自定义透视）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3120835/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pivot rows into columns (custom pivoting)
提问by Babu
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代码吗？
采纳答案by Tom H
You could probably do it with the PIVOT function, but I prefer the old school method:
你可以用 PIVOT 函数来做，但我更喜欢老派的方法：
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
- 我更改了一些列名以避免保留字
回答by Martin Smith
Just incase you do want the new school method. (The Pivot statement should work in SQL2005+, the VALUESbit for the example data only SQL2008)
以防万一你确实想要新的学校方法。（Pivot 语句应该在 SQL2005+ 中工作，VALUES示例数据的位仅用于 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
回答by Brian Hooper
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.
但我不能说我非常喜欢它。不过，总比没有好。
回答by Baaju
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
使用交叉应用以逗号分隔格式获取单列中的所有值。而不是“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.
您将在 Mon 的一列中获得 [Ch,Ph,Mth,CS2,Lab1,Lab2,Lab3]，依此类推......您可以将其用作表来查询任何特定的日期。
Hope this helps
希望这可以帮助
回答by Flipping
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]
回答by mr_eclair
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;
