So the ConnectionID and SPID will be converted into one row from multiple rows but the rows of records on VersionNumber, Cost, and Year will repeatedly be converted into columns.
As to the order of the values (VersionNumbers), SQL Server has no concept of the "first row in". Thus, I had to order by the VersionNumber. If you have an identity or datetime of insert, you could use that instead to order by to get the rows to come out in the same order they were entered.
SELECT
ConnectionID, SPID,
MAX(CASE WHEN row_num = 1 THEN VersionNumber END) AS VersionNumber,
MAX(CASE WHEN row_num = 1 THEN Cost END) AS Cost,
MAX(CASE WHEN row_num = 1 THEN Year END) AS Year,
MAX(CASE WHEN row_num = 2 THEN VersionNumber END) AS VersionNumber,
MAX(CASE WHEN row_num = 2 THEN Cost END) AS Cost,
MAX(CASE WHEN row_num = 2 THEN Year END) AS Year,
MAX(CASE WHEN row_num = 3 THEN VersionNumber END) AS VersionNumber,
MAX(CASE WHEN row_num = 3 THEN Cost END) AS Cost,
MAX(CASE WHEN row_num = 3 THEN Year END) AS Year,
MAX(CASE WHEN row_num = 4 THEN VersionNumber END) AS VersionNumber,
MAX(CASE WHEN row_num = 4 THEN Cost END) AS Cost,
MAX(CASE WHEN row_num = 4 THEN Year END) AS Year,
MAX(CASE WHEN row_num = 5 THEN VersionNumber END) AS VersionNumber,
MAX(CASE WHEN row_num = 5 THEN Cost END) AS Cost,
MAX(CASE WHEN row_num = 5 THEN Year END) AS Year
/*, ... */
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ConnectionID, SPID ORDER BY Year, VersionNumber) AS row_num
FROM #SPID
) AS S
GROUP BY ConnectionID, SPID
Ideally this should really be done in the front end or middle tier.
If you really have to do it in SQL, it can also be done dynamically.
If you want to use the result set then the column names need to be unique.
DECLARE @SQL nvarchar(max) = '';
WITH Counts
AS
(
SELECT ConnectionID, SPID, COUNT(*) AS Num
FROM SPID
GROUP BY ConnectionID, SPID
)
,MaxNum
AS
(
SELECT MAX(Num) AS N
FROM Counts
)
,Numbers
--List of numbers for demo purposes.
--In practice should probably use a TVF
-- eg https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
AS
(
SELECT number
FROM [master].dbo.spt_values
WHERE [type] = 'P'
)
SELECT @SQL = @SQL
+ NCHAR(9) + N',MAX(CASE WHEN rn = ' + X.N + N' THEN VersionNumber END) AS VersionNumber' + X.N + NCHAR(13) + NCHAR(10)
+ NCHAR(9) + N',MAX(CASE WHEN rn = ' + X.N + N' THEN Cost END) AS Cost' + X.N + NCHAR(13) + NCHAR(10)
+ NCHAR(9) + N',MAX(CASE WHEN rn = ' + X.N + N' THEN [Year] END) AS Year' + X.N + NCHAR(13) + NCHAR(10)
FROM Numbers N
CROSS JOIN MaxNum M
CROSS APPLY ( VALUES( CAST(N.number AS nvarchar(20) )) ) X(N)
WHERE N.Number BETWEEN 1 AND M.N
--print @sql
SET @SQL = N'WITH RowOrd
AS
(
SELECT ConnectionID, SPID
,VersionNumber, Cost, [Year]
,ROW_NUMBER() OVER (PARTITION BY ConnectionID, SPID ORDER BY VersionNumber) AS rn
FROM SPID
)
SELECT ConnectionID, SPID
' + @SQL + N'FROM RowOrd
GROUP BY ConnectionID, SPID;
'
--print @sql
EXEC sp_executesql @SQL;
WITH RowOrd
AS
(
SELECT ConnectionID, SPID
,VersionNumber, Cost, [Year]
,ROW_NUMBER() OVER (PARTITION BY ConnectionID, SPID ORDER BY VersionNumber) AS rn
FROM SPID
)
SELECT ConnectionID+SPID --Added + sign to turn the output as flatfile
' + @SQL + N'
FROM RowOrd
GROUP BY ConnectionID, SPID;
'
print @sql