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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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. 😉

  • 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


    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 59 total)

You must be logged in to reply to this topic. Login to reply