我正在尝试在不使用聚合函数的情况下对表进行透视
我尝试了 2 个枢轴来获取最大和最小名称,但它不起作用
Declare @test table()
Name varchar(30) not null,
Grade varchar(10) not null,
Subject varchar(10) not null
)
insert into @test values('Ami', 'HD', 'Java')
insert into @test values('Ami', 'D', 'C++')
insert into @test values('Bec', 'D', 'Java')
insert into @test values('Bec', 'P', 'C++')
insert into @test values('Bec', 'Pa', 'C++')
select * from @test
select Name, Max(case Subject when 'C++' then Grade end) 'C++',
Max(case Subject when 'Java' then Grade end) 'Java'
from @test
group by Name
SELECT * FROM
(
select Name, grade, subject from @test
)x
PIVOT
(
MIN(grade)
for subject IN ([JAVA],[C++])
)p
您可以使用 CTE 添加一个ROW_NUMBER
值,然后将其添加到您的GROUP BY
. 我还将逻辑更改为交叉表,因为它们比内置PIVOT
运算符灵活得多:
WITH CTE AS(
SELECT [Name],
Grade,
Subject,
ROW_NUMBER() OVER (PARTITION BY [Name], Subject ORDER BY Grade) AS RN
FROM @test t)
SELECT [Name],
MAX(CASE Subject WHEN 'C++' THEN Grade END) AS [C++],
MAX(CASE Subject WHEN 'Java' THEN Grade END) AS [Java]
FROM CTE
GROUP BY [Name],
RN;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句