很难描述我想知道什么。粗略地说，它是"将每组行选为一行"。
请参考链接的图片。
谢谢！！
单击此处获取图像：SQL Server，将每组行选择为一个单独的行
德博。源表
group column1 column2
------------------------
A A1 AA1
A A2 AA2
A A3 AA3
A A4 AA4
A A5 AA5
B B1 BB1
B B2 BB2
B B3 BB3
B B4 BB4
B B5 BB5
C C1 CC1
C C2 CC2
C C3 CC3
C C4 CC4
C C5 CC5
D D1 DD1
D D2 DD2
D D3 DD3
D D4 DD4
D D5 DD5
期望的结果：
group result1 result2 result3 result4 result5 result6 result7 result8 result9 result10
A A1 AA1 A2 AA2 A3 AA3 A4 AA4 A5 AA5
B B1 BB1 B2 BB2 B3 BB3 B4 BB4 B5 BB5
C C1 CC1 C2 CC2 C3 CC3 C4 CC4 C5 CC5
D D1 DD1 D2 DD2 D3 DD3 D4 DD4 D5 DD5
没有简单的方法可以透视多个列。但是有一个技巧：
您可以连接两列，并对串联的值进行透视。甚至更好：您可以让串联的值看起来像 XML(无需将其转换为真正的 XML(，并对此进行透视。此方法可以轻松恢复值类型安全。
但我必须承认：这是相当多的打字：-(
DECLARE @tbl TABLE(groupColumn VARCHAR(1),Column1 VARCHAR(2),Column2 VARCHAR(3));
INSERT INTO @tbl VALUES
('A','A1','AA1')
,('A','A2','AA2')
,('A','A3','AA3')
,('A','A4','AA4')
,('A','A5','AA5')
,('B','B1','BB1')
,('B','B2','BB2')
,('B','B3','BB3')
,('B','B4','BB4')
,('B','B5','BB5')
,('C','C1','CC1')
,('C','C2','CC2')
,('C','C3','CC3')
,('C','C4','CC4')
,('C','C5','CC5')
,('D','D1','DD1')
,('D','D2','DD2')
,('D','D3','DD3')
,('D','D4','DD4')
,('D','D5','DD5');
SELECT p.groupColumn
,casted.Xml1.value('/x[1]','varchar(max)') AS Result1a
,casted.Xml1.value('/x[2]','varchar(max)') AS Result1b
,casted.Xml2.value('/x[1]','varchar(max)') AS Result2a
,casted.Xml2.value('/x[2]','varchar(max)') AS Result2b
,casted.Xml3.value('/x[1]','varchar(max)') AS Result3a
,casted.Xml3.value('/x[2]','varchar(max)') AS Result3b
,casted.Xml4.value('/x[1]','varchar(max)') AS Result4a
,casted.Xml4.value('/x[2]','varchar(max)') AS Result4b
,casted.Xml5.value('/x[1]','varchar(max)') AS Result5a
,casted.Xml5.value('/x[2]','varchar(max)') AS Result5b
,casted.Xml6.value('/x[1]','varchar(max)') AS Result6a
,casted.Xml6.value('/x[2]','varchar(max)') AS Result6b
,casted.Xml7.value('/x[1]','varchar(max)') AS Result7a
,casted.Xml7.value('/x[2]','varchar(max)') AS Result7b
,casted.Xml8.value('/x[1]','varchar(max)') AS Result8a
,casted.Xml8.value('/x[2]','varchar(max)') AS Result8b
,casted.Xml9.value('/x[1]','varchar(max)') AS Result9a
,casted.Xml9.value('/x[2]','varchar(max)') AS Result9b
,casted.Xml10.value('/x[1]','varchar(max)') AS Result10a
,casted.Xml10.value('/x[2]','varchar(max)') AS Result10b
FROM
(
SELECT groupColumn
,'result' + CAST(ROW_NUMBER() OVER(PARTITION BY groupColumn ORDER BY Column1,Column2) AS VARCHAR(MAX)) AS ColumnName
,'<x>' + Column1 + '</x><x>' + Column2 + '</x>' AS PivotColumn
FROM @tbl
) AS pvt
PIVOT
(
MIN(PivotColumn) FOR ColumnName IN(result1,result2,result3,result4,result5,result6,result7,result8,result9,result10)
) AS p
OUTER APPLY
(
SELECT CAST(p.result1 AS XML) AS Xml1
,CAST(p.result2 AS XML) AS Xml2
,CAST(p.result3 AS XML) AS Xml3
,CAST(p.result4 AS XML) AS Xml4
,CAST(p.result5 AS XML) AS Xml5
,CAST(p.result6 AS XML) AS Xml6
,CAST(p.result7 AS XML) AS Xml7
,CAST(p.result8 AS XML) AS Xml8
,CAST(p.result9 AS XML) AS Xml9
,CAST(p.result10 AS XML) AS Xml10
) AS casted
结果
groupColumn Result1a Result1b Result2a Result2b Result3a Result3b Result4a Result4b Result5a Result5b Result6a Result6b Result7a Result7b Result8a Result8b Result9a Result9b Result10a Result10b
A A1 AA1 A2 AA2 A3 AA3 A4 AA4 A5 AA5 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
B B1 BB1 B2 BB2 B3 BB3 B4 BB4 B5 BB5 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
C C1 CC1 C2 CC2 C3 CC3 C4 CC4 C5 CC5 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
D D1 DD1 D2 DD2 D3 DD3 D4 DD4 D5 DD5 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
这是执行此操作的一种方法：
CREATE TABLE #tt(id INT IDENTITY(1,1),[group] VARCHAR(16),[column1] VARCHAR(16),[column2] VARCHAR(16))
INSERT INTO #tt([group],[column1],[column2])VALUES
('A','A1','AA1'),
('A','A2','AA2'),
('A','A3','AA3'),
('A','A4','AA4'),
('A','A5','AA5'),
('B','B1','BB1'),
('B','B2','BB2'),
('B','B3','BB3'),
('B','B4','BB4'),
('B','B5','BB5'),
('C','C1','CC1'),
('C','C2','CC2'),
('C','C3','CC3'),
('C','C4','CC4'),
('C','C5','CC5'),
('D','D1','DD1'),
('D','D2','DD2'),
('D','D3','DD3'),
('D','D4','DD4'),
('D','D5','DD5');
;WITH group_order AS (
SELECT
*,
group_order_id=ROW_NUMBER() OVER (PARTITION BY [group] ORDER BY id)
FROM
#tt
)
SELECT
[group],
result1=MAX(CASE WHEN group_order_id=1 THEN [column1] END),
result2=MAX(CASE WHEN group_order_id=1 THEN [column2] END),
result3=MAX(CASE WHEN group_order_id=2 THEN [column1] END),
result4=MAX(CASE WHEN group_order_id=2 THEN [column2] END),
result5=MAX(CASE WHEN group_order_id=3 THEN [column1] END),
result6=MAX(CASE WHEN group_order_id=3 THEN [column2] END),
result7=MAX(CASE WHEN group_order_id=4 THEN [column1] END),
result8=MAX(CASE WHEN group_order_id=4 THEN [column2] END),
result9=MAX(CASE WHEN group_order_id=5 THEN [column1] END),
result10=MAX(CASE WHEN group_order_id=5 THEN [column2] END)
FROM
group_order
GROUP BY
[group];
DROP TABLE #tt;
结果：
+-------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+
| group | result1 | result2 | result3 | result4 | result5 | result6 | result7 | result8 | result9 | result10 |
+-------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+
| A | A1 | AA1 | A2 | AA2 | A3 | AA3 | A4 | AA4 | A5 | AA5 |
| B | B1 | BB1 | B2 | BB2 | B3 | BB3 | B4 | BB4 | B5 | BB5 |
| C | C1 | CC1 | C2 | CC2 | C3 | CC3 | C4 | CC4 | C5 | CC5 |
| D | D1 | DD1 | D2 | DD2 | D3 | DD3 | D4 | DD4 | D5 | DD5 |
+-------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+