在SQL中将行转换为列 [英] Convert rows to columns in SQL
本文介绍了在SQL中将行转换为列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
请问我该怎么做?
推荐答案
You can use the PIVOT
table operator to do transform the rows of these course's grades into column, like so:
SELECT
Course,
[1] AS "Grade 1",
[2] AS "Grade 2",
[3] AS "Grade 3",
[4] AS "Grade 4"
FROM
(
SELECT
Course,
Grade,
ROW_NUMBER() OVER(PARTITION BY Course
ORDER BY COURSE) rownum
FROM Grades
) t
PIVOT
(
MAX(Grade)
FOR rownum IN([1], [2], [3], [4])
) p;
SQL小提琴演示
这将为您提供:
SQL Fiddle Demo
This will give you:
COURSE GRADE 1 GRADE 2 GRADE 3 GRADE 4
A 15 17 (null) (null)
B 12 (null) (null) (null)
C 10 18 0 9
请注意::我使用了排名函数 <与PARTITION BY Course
一起使用strong> ROW_NUMBER()
将其分为四个等级.这样您就可以将它们分为这四个组.
Note that: I used the ranking function ROW_NUMBER()
with PARTITION BY Course
to partition them into four grades. So that you could then group them into these four groups.
如果您需要动态进行此操作,以防出现未知数量的成绩.在这种情况下,您可以使用动态SQL来动态生成成绩列表,如下所示:
If you need to this dynamically, in case there was unknown number of grades. You can use dynamic SQL in this case to generate the list of grades dynamically like so:
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
SELECT @cols = STUFF((SELECT distinct ','
+ QUOTENAME('Grade '
+ CAST(ROW_NUMBER() OVER(PARTITION BY Course
ORDER BY Course) AS VARCHAR(10)))
FROM Grades
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
SELECT @query = 'SELECT Course, ' + @cols +
'FROM
(
SELECT
Course,
Grade,
''Grade '' + CAST(ROW_NUMBER()
OVER(PARTITION BY Course
ORDER BY COURSE) AS VARCHAR(10)) rownum
FROM Grades
) t
PIVOT
(
MAX(Grade)
FOR rownum IN(' + @cols + ')' +
') p';
execute(@query);
这篇关于在SQL中将行转换为列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文