Robert.Sterbal (7/17/2014)
Looks like a great technique, although more than half the sites I support are SQL Server 2KI'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