正在将结果从1:n查询移动到重复列

Question

我想使用T-sql 从1:n连接的结果中实现以下内容

Surname | Givename  |..Address  | City
Name1....| Givename1.|..Addr11...|  City11
Name1....| Givename1.|..Addr12...|  City12
Name2....| Givename2.|..Addr21...|  City21
Name2....| Givename2.|..Addr22...|  City22
Name2....| Givename2.|..Addr23...|  City23

收件人：

Surname | Givename.. | Address | City... | Address | City... | Address | City
Name1....| Givename1...|  Addr11....| City11. | Addr12....| City12. |
Name2....| Givename2...|  Addr21....| City21. | Addr22....| City22. | Addr23....|  City23

我不在乎重复专栏名称。如果在重复的列中有一个数字的灵魂，那也没关系。

感谢

在我看来，Pivot不是这种情况下的解决方案。因为列名应该重新赋值，并且在pivot中，单元格值被移动到列名，这也与pivot不同，它不涉及聚合函数。

Answer 1

在我看来，以下查询将处理您的问题。但是，SQL Server对表有列数限制。

每个表的列1024个包含稀疏列集的表包括多达30000列。请参见稀疏列集。

您应该考虑一下您的数据。

DROP TABLE IF EXISTS #Test
DROP TABLE IF EXISTS ##PivotUnlimited
CREATE TABLE  #Test(Surname  VARCHAR(100) , GivenName  VARCHAR(200),
Adress  VARCHAR(100),City  VARCHAR(100))
INSERT INTO #Test
VALUES
('Name1','Givename1','Addr11','City11'),
('Name1','Givename1','Addr12','City12'),
('Name2','Givename2','Addr21','City21'),
('Name2','Givename2','Addr22','City21'),
('Name2','Givename2','Addr23','City23')
,('Name5','Givename5','Addr51','City51'),
('Name5','Givename5','Addr52','City52'),
('Name5','Givename5','Addr53','City53'),
('Name5','Givename5','Addr54','City54'),
('Name3','Givename3','Addr31','City31')
DECLARE @Counter AS INT=1
DECLARE @Max AS INT
DECLARE @SQL AS VARCHAR(MAX)
SELECT @Max= MAX(DetMax) FROM
(
SELECT  ROW_NUMBER() OVER(Partition by Surname ORDER BY Surname ) AS DetMax FROM #Test
) AS TMP

DROP TABLE IF EXISTS ##PivotUnlimited
CREATE TABLE ##PivotUnlimited (Surname VARCHAR(100),GivenName VARCHAR(100))
WHILE @Counter <=@Max
BEGIN
SET @SQL= 'ALTER TABLE ##PivotUnlimited ADD  ADDR' + CONVERT(VARCHAR,@Counter) + ' VARCHAR(100)'
EXEC(@SQL)
SET @SQL= 'ALTER TABLE ##PivotUnlimited ADD  City' + CONVERT(VARCHAR,@Counter) + ' VARCHAR(100)'
EXEC(@SQL)
SET @Counter=@Counter+1
END

INSERT INTO ##PivotUnlimited (Surname,GivenName)
SELECT DISTINCT Surname , GivenName FROM #Test
DECLARE @Name AS VARCHAR(100) 
DECLARE cursorT CURSOR
FOR
SELECT DISTINCT Surname from #test
OPEN cursorT 
FETCH NEXT FROM cursorT INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @CounterCursor AS INT=1
DECLARE @MaxCursort AS INT =0
DECLARE @UpdateSQL AS VARCHAR(MAX)
DECLARE @UptAdr AS VARCHAR(100)
DECLARE @UptCity AS VARCHAR(100)
SELECT @MaxCursort=RowNumber FROM (
SELECT  ROW_NUMBER() OVER(Partition by Surname ORDER BY Surname ) AS RowNumber FROM #Test  
WHERE  Surname=@Name
) AS TMP_TBL

WHILE @CounterCursor<= @MaxCursort
BEGIN
SELECT @UptAdr=Adress ,@UptCity=City  FROM (
SELECT  ROW_NUMBER() OVER(Partition by Surname ORDER BY Surname ) AS RowNumber,* FROM #Test  
) AS TMP_TBL WHERE RowNumber=@CounterCursor and Surname=@Name
SET @UpdateSQL= 'UPDATE ##PivotUnlimited SET ADDR' + CONVERT(VARCHAR,@CounterCursor) + ' = '  + '''' + @UptAdr +'''' + ' , ' 
+ ' City' + CONVERT(VARCHAR,@CounterCursor) + ' = '  + '''' + @UptCity +'''' +  ' WHERE Surname = ' + '''' + @Name + ''''
EXEC (@UpdateSQL)
SET @CounterCursor=@CounterCursor+1
END
FETCH NEXT FROM cursorT INTO @Name
END
CLOSE cursorT 
DEALLOCATE cursorT 
SELECT * FROM ##PivotUnlimited
+---------+-----------+--------+--------+--------+--------+--------+--------+--------+--------+
| Surname | GivenName | ADDR1  | City1  | ADDR2  | City2  | ADDR3  | City3  | ADDR4  | City4  |
+---------+-----------+--------+--------+--------+--------+--------+--------+--------+--------+
| Name1   | Givename1 | Addr11 | City11 | Addr12 | City12 | NULL   | NULL   | NULL   | NULL   |
| Name2   | Givename2 | Addr21 | City21 | Addr22 | City21 | Addr23 | City23 | NULL   | NULL   |
| Name3   | Givename3 | Addr31 | City31 | NULL   | NULL   | NULL   | NULL   | NULL   | NULL   |
| Name5   | Givename5 | Addr51 | City51 | Addr52 | City52 | Addr53 | City53 | Addr54 | City54 |
+---------+-----------+--------+--------+--------+--------+--------+--------+--------+--------+