# Convert Rows into Columns

• Comments posted to this topic are about the item Convert Rows into Columns

• Nice.

A simpler (IMHO) way of determining the ROWID is to simply subtract the Remainder from the RowNumber, a technique used a lot in "Gaps and Islands" solutions.

`select`

`max(case when remainder = 0 then LotNo else '' end),`

`max(case when remainder = 1 then LotNo else '' end),`

`max(case when remainder = 2 then LotNo else '' end)`

`from`

`(`

` select`

` LotNo`

` , ( row_number() over ( order by LotNo ) +2 ) % 3 as Remainder`

` , row_number() over ( order by LotNo ) - ( row_number() over ( order by LotNo ) +2 ) % 3 as ROWID`

` from`

` lotno`

`)a`

`group by ROWID`

• This seems really useful but the article formatting could do with a bit of improving - not many of codeblocks are correctly formatted

• Here's a shorter version. Hope it helps.

`SELECT [0] Col1,[1] Col2,[2] Col3`

`FROM (`

` SELECT LotNo`

` , ((ROW_NUMBER() OVER (ORDER BY LotNo)-1) % 3) col`

` , ((ROW_NUMBER() OVER (ORDER BY LotNo)-1) / 3) roww`

` FROM (VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z'))t(LotNo)`

` ) x`

` PIVOT (MIN(LotNo) FOR col IN ([0],[1],[2])) p`

(2008 required for VALUES)

• Wouldn't it be easier to use PIVOT and UNPIVOT to transpose the columns?

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

• I once had a similar requirement and the best option was to generate the columns in the reporting tool. That was on VFP and I'm not sure if it's possible to do it in every reporting tool available, but it would certainly be easier to maintain and understand (single column from recordset printed in several columns on the report).

However, if someone needs to do it in T-SQL, it's a good option, but certainly the CASE statement is not necessary.

`WITH MstrTable AS(`

` SELECT LotNo`

` , ( ROW_NUMBER() OVER ( ORDER BY LotNo ) + 2 ) % 3 as Remainder`

` , ( ROW_NUMBER() OVER ( ORDER BY LotNo ) + 2 ) / 3 as RowID`

` FROM LotNo`

`)`

`SELECT`

` MAX ( CASE WHEN remainder = 0 THEN LotNo ELSE '' END ) as Col1`

` ,MAX ( CASE WHEN remainder = 1 THEN LotNo ELSE '' END ) as Col2`

` ,MAX ( CASE WHEN remainder = 2 THEN LotNo ELSE '' END ) as Col3`

`FROM mstrtable`

`GROUP BY ROWID;`

`--Second option just for fun, but shouldn't be better and might be worse.`

`WITH Groups AS(`

` SELECT LotNo`

` , NTILE( (SELECT COUNT(*) FROM LotNo) / 3 + 1) OVER(ORDER BY LotNo) as RowID`

` FROM LotNo`

`),`

`mstrtable AS(`

` SELECT LotNo,`

` RowID,`

` ROW_NUMBER() OVER( PARTITION BY RowID ORDER BY LotNo) AS remainder`

` FROM Groups`

`)`

`SELECT`

` MAX ( CASE WHEN remainder = 1 THEN LotNo ELSE '' END ) as Col1`

` ,MAX ( CASE WHEN remainder = 2 THEN LotNo ELSE '' END ) as Col2`

` ,MAX ( CASE WHEN remainder = 3 THEN LotNo ELSE '' END ) as Col3`

`FROM mstrtable`

`GROUP BY ROWID;`

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• Looks like a great technique, although more than half the sites I support are SQL Server 2K

I'll have to figure out how to do this in excel (with the so I can take a column and spread it into 3.

Thanks!

• Robert.Sterbal (7/17/2014)

Looks like a great technique, although more than half the sites I support are SQL Server 2K

I'll have to figure out how to do this in excel (with the so I can take a column and spread it into 3.

Thanks!

Hey Robert,

For 2000, you could use a temp table with a new identity to ensure the gaps are not a problem.

Here's an example using the same data as the article.

`SELECT IDENTITY( int, 3, 1) AS RowNo, --Note the Identity definition starts at 3`

` LotNo`

`INTO #LotNo`

`FROM LotNo`

`SELECT MAX( CASE WHEN RowNo % 3 = 0 THEN LotNo ELSE '' END) AS Column1,`

` MAX( CASE WHEN RowNo % 3 = 1 THEN LotNo ELSE '' END) AS Column2,`

` MAX( CASE WHEN RowNo % 3 = 2 THEN LotNo ELSE '' END) AS Column3`

`FROM #LotNo`

`GROUP BY RowNo / 3`

`DROP TABLE #LotNo`

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• You really need only a single ROW_NUMBER function:

`SELECT`

` MAX(CASE WHEN row_num % 3 = 0 THEN LotNo ELSE '' END) AS Lot1,`

` MAX(CASE WHEN row_num % 3 = 1 THEN LotNo ELSE '' END) AS Lot2,`

` MAX(CASE WHEN row_num % 3 = 2 THEN LotNo ELSE '' END) AS Lot3`

`FROM (`

` SELECT LotNo, ROW_NUMBER() OVER(ORDER BY LotNo) + 2 AS row_num`

` FROM LotNo`

`) AS derived`

`GROUP BY row_num / 3`

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

• Is it possible to make this dynamic to discover the needed columns without Dynamic SQL?

• sgross 10581 (7/17/2014)

Is it possible to make this dynamic to discover the needed columns without Dynamic SQL?

No, you need to use dynamic sql to have a dynamic number of columns.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• I am bit confused here, what's preventing you to use PIVOT function here?

• vjonnal1 (7/17/2014)

I am bit confused here, what's preventing you to use PIVOT function here?

Maybe a simple preference to use cross tabs instead of pivot. That's my case at least.

Cross tabs can be faster when more than one column or calculation are used on the pivot.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• Luis, if we talking about performance difference did you try to benchmark your approach vs PIVOT?

IFAIK PIVOT became part of built-in SQL Server functionality since SQL Server 2005. 😉

Luis, if we talking about performance difference did you try to benchmark your approach vs PIVOT?

IFAIK PIVOT became part of built-in SQL Server functionality since SQL Server 2005. 😉

See the following article for just such a benchmark test. Keep in mind that it was on an older machine. Also, performance isn't the only thing to be gained by using CROSSTABs instead of PIVOTs.

http://www.sqlservercentral.com/articles/T-SQL/63681/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".