Moving result from 1:n query to repeating columns

Question

I would like to achieve the following from a result of a 1:n join using T-sql

 Surname | Givename  |..Address  | City

Name1....| Givename1.|..Addr11...|  City11

Name1....| Givename1.|..Addr12...|  City12

Name2....| Givename2.|..Addr21...|  City21

Name2....| Givename2.|..Addr22...|  City22

Name2....| Givename2.|..Addr23...|  City23

TO:

Surname | Givename.. | Address | City... | Address | City... | Address | City

Name1....| Givename1...|  Addr11....| City11. | Addr12....| City12. |

Name2....| Givename2...|  Addr21....| City21. | Addr22....| City22. | Addr23....|  City23

I not care about repeating columnames. Up if there is a soultion with numbers in the repeating columns it would be fine too.

Thanks

In my opinion Pivot is not a solution in this case. Because the column name should repat, and in pivot, cell values are moved to column names, also is unlike pivot no aggregate function involved.

Google "SQL pivot query" to get started, and maybe [read this SO question](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query). — Tim Biegeleisen, Nov 01 '19 at 09:32
You have columns with the same name here, a column's name *should* be unique within the dataset. Your rows seem to also have variable number of columns; that isn't allowed. — Thom A, Nov 01 '19 at 09:32
Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Ankit Bajpai, Nov 01 '19 at 11:01
n is in praxis not more then 5. But i am looking for a dynamic solution, that would also work with undefined numeber of n. — Alpha8, Nov 01 '19 at 11:21
I know you don't think this is a `PIVOT`, but _tons_ of `PIVOT`s include entirely useless `MIN()` or `MAX()` arguments that don't change the data in order to get the `PIVOT` functionality. And you're going to have to work a little harder at the column names, but all the same, this is a `PIVOT`. — Eric Brandt, Nov 01 '19 at 13:25

score 0 · Answer 1 · answered Nov 02 '19 at 10:14

In my thought, the following query will handle your issue. However the SQL Server has column number limit for tables.

Columns per table 1,024 Tables that include sparse column sets include up to 30,000 columns. See sparse column sets.

You should take this considiration for your data.

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 |
+---------+-----------+--------+--------+--------+--------+--------+--------+--------+--------+

Moving result from 1:n query to repeating columns

1 Answers1