July 17, 2014 at 12:00 am
Comments posted to this topic are about the item Convert Rows into Columns
July 17, 2014 at 4:09 am
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
July 17, 2014 at 5:53 am
This seems really useful but the article formatting could do with a bit of improving - not many of codeblocks are correctly formatted
July 17, 2014 at 9:02 am
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)
July 17, 2014 at 9:02 am
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
July 17, 2014 at 9:32 am
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;
July 17, 2014 at 12:24 pm
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!
July 17, 2014 at 12:43 pm
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
July 17, 2014 at 1:30 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 17, 2014 at 1:38 pm
Is it possible to make this dynamic to discover the needed columns without Dynamic SQL?
July 17, 2014 at 2:36 pm
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.
July 17, 2014 at 3:23 pm
I am bit confused here, what's preventing you to use PIVOT function here?
July 17, 2014 at 3:51 pm
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.
July 17, 2014 at 5:46 pm
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. 😉
July 17, 2014 at 5:58 pm
fregatepllada (7/17/2014)
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 58 total)
You must be logged in to reply to this topic. Login to reply