如何水平分布 SQL 结果?

Question

我在SQL Server中有一个这样的表(所有列都是数字(：

Id1 | Id2 | Number | Qty
----+-----+--------+-------
1 |  1  | 100001 | 100
1 |  2  | 100002 | 110
1 |  3  | 100003 | 120
2 |  1  | 100004 | 130
2 |  2  | 100005 | 200
2 |  3  | 100006 | 300
2 |  4  | 100007 | 400

我希望结果分布在另外 2 列中，如下所示(没有 Id(：

Number1 | Qty1  | Number2 | Qty2  | Number3 | Qty3 
--------+-------|---------|-------|---------|------
100001 | 100   | 100002  | 110   | 100003  | 120
100004 | 130   | 100005  | 200   | 100006  | 300
100007 | 400   |  null   | null  |  null   | null

可能吗？ 谢谢。

Answer 1

你可以尝试这样的事情：

SELECT CASE WHEN id2 = 1 THEN number ELSE '' END AS 'Number1',
CASE WHEN id2 = 1 THEN Qty ELSE '' END AS 'Qty1',
CASE WHEN id2 = 2 THEN number ELSE '' END AS 'Number2',
CASE WHEN id2 = 2 THEN Qty ELSE '' END AS 'Qty2',
CASE WHEN id2 = 3 THEN number ELSE '' END AS 'Number3',
CASE WHEN id2 = 3 THEN Qty ELSE '' END AS 'Qty3'
FROM tablename

Answer 2

唯一想到的是递归 CTE。 这将允许您使用 Id2 中的 n 值。您确定最后一行的 Id2 值正确且值为 4 吗？

Answer 3

你需要的是一个枢轴。这是链接： https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

Answer 4

这有用吗？

DECLARE @T AS TABLE
(
id1 INT, 
id2 int ,
Number INT,
Qty Int
)
Insert into @T
SELECT 1,1,100001, 100 Union All
SELECT 1,2,100002, 110 Union All
SELECT 1,3,100003, 120 Union All
SELECT 2,1,100004, 130 Union All
SELECT 2,2,100005, 200 Union All
SELECT 2,3,100006, 300 Union All
SELECT 2,4,100007, 400 
;with cte1 as
(
select *,
LEAD(Number) OVER(partition by id1 order by id2) as leadingNumber,
LEAD(Qty) OVER(partition by id1 order by id2) as LeadingQty,
ROW_NUMBER()over(partition by id1 order by (select null)) as rowNum
from @T
)
,
cte2
As
(
select 
c1.id1,c1.Id2,
c1.Number As Number1,c1.Qty as Qty1,c1.leadingNumber As Number2, c1.LeadingQty as Qty2
,LEAD(leadingNumber) OVER(partition by id1 order by id2)  as Number3,
LEAD(LeadingQty) OVER(partition by id1 order by id2) as Qty3
from cte1 c1
)
Select Number1,Qty1,Number2,Qty2,Number3,Qty3
from cte2 where id2%3=1

Answer 5

好的。经过一些研究，我找到了生成动态列的解决方案，并使用PIVOT解决了我的问题。感谢大家的提示。

DECLARE  @cols  AS NVARCHAR(MAX),  @query  AS NVARCHAR(MAX), @Param1 AS int
SET @cols = STUFF((SELECT  ',' + QUOTENAME(number) FROM View_GRTLinhasGRTLinhasTela WHERE id1 = @Param1 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')  
SET @query = 'SELECT *
FROM(
SELECT p.number, p.qty FROM View_GRTLinhasGRTLinhasTela p
WHERE p.id1 = @Param1) AS sourcetable
PIVOT (
SUM(qty) 
FOR number IN (' + @cols + ') ) AS pivottable'
EXEC sp_executesql @query