如何在SQL Server中使用数据透视表(无聚合)? [英] How to use pivot in sql server (without aggregates )?
本文介绍了如何在SQL Server中使用数据透视表(无聚合)?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
请帮助我解决此问题:
您将得到一个包含两列的表:
列是以下内容之一:
please help me solve this problem: You are given a table, containing two columns: column is one of the followings:
Doctor
Professor
Singer
Actor
编写查询以在相应的occ下输出名称。格式如下:
Write a query to output the names underneath the corresponding occ. in the following format:
+--------+-----------+--------+------+
| Doctor | Professor | Singer | Actor|
+--------+-----------+--------+------+
名称必须按字母顺序排列。
Names must be listed in alphabetically sorted order.
样本输入
Name Occupation
Meera Singer
Ashely Professor
Ketty Professor
Christeen Professor
Jane Actor
Jenny Doctor
Priya Singer
样本输出
Jenny Ashley Meera Jane
Samantha Christeen Priya Julia
NULL Ketty NULL Maria
注意
打印 NULL
我尝试使用:
SELECT *
FROM
(
SELECT [Name], [Occupation]
FROM occupations
) AS source
PIVOT
(
max([Name])
FOR [occupation] IN ([Doctor], [Professor], [Singer], [Actor])
) as pvt;
给出以下输出:
Priya Priyanka Kristeen Samantha
如何解决?
推荐答案
您只需要根据名称的职业和字母顺序给每个名称指定一个行号即可。然后将该行号包括在数据透视查询中
You just need to give each name a row number based on their occupation and order alphabetically.. then include that row number in your pivot query.
CREATE TABLE Occupations (
NAME VARCHAR(MAX),
Occupation VARCHAR(MAX)
)
INSERT INTO Occupations
VALUES
('Samantha','Doctor'),
('Julia','Actor'),
('Maria','Actor'),
('Meera','Singer'),
('Ashley','Professor'),
('Ketty','Professor'),
('Christeen','Professor'),
('Jane','Actor'),
('Jenny','Doctor'),
('Priya','Singer');
SELECT
[Doctor],
[Professor],
[Singer],
[Actor]
FROM
(SELECT
ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) rn,
[Name],
[Occupation]
FROM
Occupations
) AS source
PIVOT
( MAX([Name]) FOR [occupation] IN ([Doctor],[Professor],[Singer],[Actor]) ) as pvt
ORDER BY rn
DROP TABLE Occupations
这篇关于如何在SQL Server中使用数据透视表(无聚合)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文