SQLTeam.com | Weblogs | Forums

Split rows into columns

Hi all,

How do I split from Rows to columns on the below dataset?

CREATE TABLE SPID
(
ConnectionID VARCHAR(3)
,SPID VARCHAR(3)
,VersionNumber CHAR(1)
,Cost Money
,[Year] VARCHAR(4)
)

INSERT INTO SPID (ConnectionID,SPID,VersionNumber,Cost,[Year])
VALUES ('123','4','2','12500','2020'),('123','4','3','35000','2020'),('123','4','4','12400','2020'),('123','4','6','11200','2020'),('123','4','5','13500','2020')

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.

Thank you

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
1 Like

Scott, excellent help! This is great and working, thank you, Scott!!!!

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;
2 Likes

You can consolidate those two queries into a single query:

;WITH MaxNum AS (
    SELECT TOP (1) COUNT(*) AS N /* UGH! I *hate* single-char names */
    FROM SPID
    GROUP BY ConnectionID, SPID
    ORDER BY COUNT(*) DESC
)
2 Likes

Thank you Ifor, I did try it, and also worked!!!

Hi Ifor, if I want the output as a flatfile, how do turn the ouput from grid to flatfile?

I can add the + sign in between the column ConnectionID, SPID but not able to do for the rest columns,

This is from my testing:

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 --Added + sign to turn the output as flatfile
' + @SQL + N'
FROM RowOrd
GROUP BY ConnectionID, SPID;
'
print @sql

EXEC sp_executesql @SQL;