如何在sql server中创建所需的查询 [英] How create the required query in sql server

问题描述

大家好，我在sql server中有这样的数据 -

< th>字母

Num
1	A
1	B
2	C
2	D
2	E
3	F

你能帮我做一个SQL查询来显示这样的数据 -





< td>

Alpha1	Alpha2	Alpha3
A	C	F
B	D
E

提前致谢。我尝试了一些东西，但无法得到这种格式。

Hello guys, I have some data in sql server like this -

Num	Alphabet
1	A
1	B
2	C
2	D
2	E
3	F

Can you help me make an SQL query that will display the data like this -

Alpha1	Alpha2	Alpha3
A	C	F
B	D
	E

Thanks in advance. I tried some things but cannot get this exact format.

推荐答案

检查：

Check this:

DECLARE @tmp TABLE (Num INT, Alphabet VARCHAR(5))

INSERT INTO @tmp (Num, Alphabet)
VALUES(1, 'A'),
(1, 'B'),
(2, 'C'),
(2, 'D'),
(2, 'E'),
(3, 'F')

SELECT Num, [1] AS Alpha1, [2] AS Alpha2, [3]  AS Alpha3
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Num ORDER BY Alphabet) AS Alpha
    FROM @tmp
    ) AS DT
PIVOT (MAX(Alphabet) FOR Alpha IN([1], [2], [3])) AS PT

结果：

Result:

Num Alpha1  Alpha2  Alpha3
1   A       B       NULL
2   C       D       E
3   F       NULL    NULL

这看起来像家庭作业，所以不 - 我们赢了;只是给你解决方案。

首先看 PIVOT [ ^ ]并自己尝试一下！

This looks like homework, so no - we won;t just give you teh solution.
Start by looking at PIVOT[^] and give it a try yourself!

这篇关于如何在sql server中创建所需的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！