Convert Rows into Columns

  • Larry.Hennig (12/11/2015)


    Columnar output that is row-ordered is difficult to read,

    I absolutely agree with that but only if such order is confined to individual pages. Just for grins, try the same thing with a million rows broken by 60 line page.

    --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)

  • Ok... I realize that this is an older thread but people are still posting PIVOTs to do this and that's going to be slower than a good CROSSTAB. The problem is that the CROSSTABs that have been posted haven't been so hot and the best that's happened with all the code posted so far (well, except for the one that Larry.Hennig was kind enough to just make a posting for) is that the various CROSSTAB methods have mostly only tied the better PIVOT methods so far. As Scott Pletcher points out, part of the problem is that almost everyone has been using more than one ROW_NUMBER() and that's just not necessary.

    But, even with using just one ROW_NUMBER(), even Scott's good CROSSTAB code was only neck and neck with the fastest (they take turns winning) PIVOT code written by Clif.Johnson and pcanino70 .

    You can see all of this by running the following code, which creates a million row table similar to the original in the article (except I use GUIDs converted to VARCHAR to give it a little more bulk for testing and then runs almost all (saving one for later) of the various methods that actually accomplished the task cited in the original article for this discussion. It's all self-contained and runs in TempDB so we don't take a chance on dropping a real table just before building it.

    RAISERROR('

    --==================================================================================================

    Building the test data...

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== Do this in a nice, safe place that everyone has.

    USE tempdb

    ;

    --===== If the test table already exists, drop it to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb.dbo.LotNo','U') IS NOT NULL

    DROP TABLE dbo.LotNo

    ;

    --===== Create and populate the table on-the-fly.

    SELECT TOP 1000000

    IDNo = IDENTITY(INT,1,7) --Increment by 7 to simulate gaps in IDs

    , LotNo = CAST(NEWID() AS VARCHAR(50))

    INTO dbo.LotNo

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    ;

    RAISERROR('

    --==================================================================================================

    Baseline Test with just the straight data...

    This also "primes the pump" so that everyone has an equal chance.

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    SELECT Column1 = LotNo

    , Column2 = LotNo

    , Column3 = LotNo

    INTO #Target1

    FROM dbo.LotNo

    ;

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    Code from the article...

    (CROSSTAB - Multiple ROW_NUMBER())

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    with MstrTable ( LotNo, Remainder, ROWID )

    as ( select

    LotNo

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

    , case when ( row_number() over ( order by LotNo ) + 2 ) % 3 = 0

    then ( row_number() over ( order by LotNo ) + 2 )

    else case when ( row_number() over ( order by LotNo )

    + 2 ) % 3 = 1

    then ( row_number() over ( order by LotNo )

    + 1 )

    else ( row_number() over ( order by LotNo ) )

    end

    end as RowID

    from

    dbo.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

    INTO #Target1 --ADDED THIS

    from

    mstrtable

    group by

    ROWID

    ;

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    Kev Riley

    (CROSSTAB - 2 ROW_NUMBER() in FROM Clause)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    select --ADDED COLUMN NAMES

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

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

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

    INTO #Target1 --ADDED THIS

    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

    dbo.LotNo

    )a

    group by ROWID

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    Clif.Johnson-989960

    (PIVOT - 2 ROW_NUMBER() in FROM Clause)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

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

    INTO #Target1 --ADDED THIS

    FROM (

    SELECT LotNo

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

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

    FROM dbo.LotNo

    ) x

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

    ;

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    Luis Cazares (Option 1)

    (CROSSTAB - 2 ROW_NUMBER() in CTE)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    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 dbo.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

    INTO #Target1 --ADDED THIS

    FROM mstrtable

    GROUP BY ROWID;

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    Luis Cazares (Option 2)

    (CROSSTAB with NTILE() and ROW_NUMBER())

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    WITH Groups AS(

    SELECT LotNo

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

    FROM dbo.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

    INTO #Target1 --ADDED THIS

    FROM mstrtable

    GROUP BY ROWID;

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    ScottPletcher

    (CROSSTAB with single ROW_NUMBER() in FROM clause)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    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

    INTO #Target1 --ADDED THIS

    FROM (

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

    FROM dbo.LotNo

    ) AS derived

    GROUP BY row_num / 3

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    zbychbor

    (CROSSTAB - Multiple ROW_NUMBER() and 3 calls to the CTE using self joins)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    ;with lot(lotNo) as (

    select lotNo

    FROM dbo.LotNo

    )

    , cte02 as (

    select rn = row_number() over (order by lotNo)

    ,rn2 = 1 + (-1 + row_number() over (order by lotNo)) % 3

    ,rn3 = 1 + (-1 + row_number() over (order by lotNo)) / 3

    ,*

    from lot

    )

    , cte03 as (

    select rn = C1.rn

    , L1 = C1.lotNo

    , L2 = c2.lotNo

    , L3 = c3.lotNo

    from cte02 as C1

    left join cte02 as C2 on C1.rn3 = C2.rn3 and c2.rn2 = 2

    left join cte02 as C3 on C1.rn3 = C3.rn3 and c3.rn2 = 3

    where c1.rn2 = 1

    )

    select L1

    ,L2 = isnull(L2, '')

    ,L3 = isnull(L3, '')

    INTO #Target1 --ADDED THIS

    from cte03

    order by rn

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    pcanino70

    (PIVOT - 2 ROW_NUMBER() in CTE)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    with cte

    as

    (

    select LotNo

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

    , ( row_number() over ( order by LotNo ) - 1) / 3 as Category

    from dbo.LotNo

    )

    select Category, [0] as column1, [1] as column2, [2] as column3

    INTO #Target1 -- ADDED THIS

    from cte

    pivot

    (

    min(LotNo) for Remainder in ([0],[1],[2])

    ) as piv

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    Wilson.Mead

    (PIVOT)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    IF OBJECT_ID('tempdb..#ColumnNames','U') IS NOT NULL

    DROP TABLE #ColumnNames

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    CREATE TABLE #ColumnNames (ColumnKey int, Columnname VARCHAR(10))

    DELETE #ColumnNames

    INSERT INTO #ColumnNames (ColumnKey, ColumnName)

    Values (0, 'Column1')

    , (1, 'Column2')

    , (2, 'Column3')

    SELECT max(column1) Lot1, max(column2) Lot2, max(column3) Lot3

    INTO #Target1 --ADDED THIS

    FROM (

    SELECT Source.LotNo, source.ColumnKey, Source.RowKey, map.Columnname FROM (

    SELECT LotNo

    , (Row_Number() over (order by LotNo) + 2) / 3 as RowKey

    , (Row_Number() over (order by LotNo) - 1) % 3 as ColumnKey

    FROM dbo.LotNo

    ) Source

    INNER JOIN #ColumnNames Map on Map.ColumnKey = Source.ColumnKey %3

    ) AS ResultSet

    PIVOT (

    MAX (LotNo) FOR ColumnName in (Column1, Column2, Column3)

    ) as it

    group by RowKey

    SET STATISTICS TIME OFF

    ;

    GO

    Here are the run results that I was talking about. They don't show any particular advantage of CROSSTAB over pivot (yet).

    --==================================================================================================

    Building the test data...

    --==================================================================================================

    (1000000 row(s) affected)

    --==================================================================================================

    Baseline Test with just the straight data...

    This also "primes the pump" so that everyone has an equal chance.

    --==================================================================================================

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server Execution Times:

    CPU time = 985 ms, elapsed time = 987 ms.

    (1000000 row(s) affected)

    --==================================================================================================

    Code from the article...

    (CROSSTAB - Multiple ROW_NUMBER())

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 15125 ms, elapsed time = 5330 ms.

    (333334 row(s) affected)

    --==================================================================================================

    Kev Riley

    (CROSSTAB - 2 ROW_NUMBER() in FROM Clause)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 14435 ms, elapsed time = 4950 ms.

    (333334 row(s) affected)

    --==================================================================================================

    Clif.Johnson-989960

    (PIVOT - 2 ROW_NUMBER() in FROM Clause)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 13203 ms, elapsed time = 4625 ms.

    (333334 row(s) affected)

    --==================================================================================================

    Luis Cazares (Option 1)

    (CROSSTAB - 2 ROW_NUMBER() in CTE)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 14313 ms, elapsed time = 4758 ms.

    (333334 row(s) affected)

    --==================================================================================================

    Luis Cazares (Option 2)

    (CROSSTAB with NTILE() and ROW_NUMBER())

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 16218 ms, elapsed time = 10088 ms.

    (333334 row(s) affected)

    --==================================================================================================

    ScottPletcher

    (CROSSTAB with single ROW_NUMBER() in FROM clause)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 13501 ms, elapsed time = 4411 ms.

    (333334 row(s) affected)

    --==================================================================================================

    zbychbor

    (CROSSTAB - Multiple ROW_NUMBER() and 3 calls to the CTE using self joins)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 18828 ms, elapsed time = 19000 ms.

    (333334 row(s) affected)

    --==================================================================================================

    pcanino70

    (PIVOT - 2 ROW_NUMBER() in CTE)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 13578 ms, elapsed time = 4698 ms.

    (333334 row(s) affected)

    --==================================================================================================

    Wilson.Mead

    (PIVOT)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 15 ms, elapsed time = 1 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    (0 row(s) affected)

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    (3 row(s) affected)

    SQL Server parse and compile time:

    CPU time = 16 ms, elapsed time = 16 ms.

    SQL Server Execution Times:

    CPU time = 18062 ms, elapsed time = 5708 ms.

    Warning: Null value is eliminated by an aggregate or other SET operation.

    (333334 row(s) affected)

    [font="Arial Black"]A Faster CROSSTAB[/font]

    Like I said, the results don't show any particular advantage of CROSSTAB over PIVOT and yet I'm still going to insist that CROSSTAB is about 38% faster and uses about 36% less CPU time than PIVOTs. Run the following code and see the results there.

    RAISERROR('

    --==================================================================================================

    Jeff Moden

    (CROSSTAB - 1 ROW_NUMBER in CTE)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    WITH

    cteEnumerate AS

    (

    SELECT RowNum = ROW_NUMBER() OVER (ORDER BY IDNo)-1

    ,LotNo

    FROM dbo.LotNo

    )

    SELECT Column1 = MAX(CASE WHEN RowNum%3 = 0 THEN LotNo ELSE '' END)

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

    , Column3 = MAX(CASE WHEN RowNum%3 = 2 THEN LotNo ELSE '' END)

    INTO #Target1

    FROM cteEnumerate

    GROUP BY RowNum/3

    ;

    SET STATISTICS TIME OFF

    ;

    GO

    Here are the results from the above.

    --==================================================================================================

    Jeff Moden

    (CROSSTAB - 1 ROW_NUMBER in CTE)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 9657 ms, elapsed time = 3345 ms.

    (333334 row(s) affected)

    If you check the Actual Execution Plans between Scott's CROSSTAB with the ROW_NUMBER() in the FROM clause and mine with the ROW_NUMBER() in the CTE, you'll only see minor differences between them. Someone like Grant Fritchey could explain it much better but what I saw was that the optimizer over estimated the size of the rows for Scott's code. I've not done a deep enough dive to explain better than that but the CTE version also always came out the fastest in the article I posted earlier in this thread.

    Can we make the PIVOT run a bit faster by limiting it to only a single ROW_NUMBER()? I'm thinking "NOT" but I welcome someone to try and report the test results compared to the CTE CROSSTAB that I wrote above.

    [font="Arial Black"]And now... the rest of the story.[/font]

    Now, I told you that story to tell you this one. A long time ago when 2005 first came out, a good friend of mine who goes by the handle of "Sergiy" on this forum and I took on all comers for similar testing except we beat the tar out of other folks methods that did use ROW_NUMBER() by using much older tricks that most have forgotten by now. Luis Cazares didn't forget the method when someone asked how to solve this problem in SQL Server 2000 where ROW_NUMBER() didn't yet exist but I'm not sure that he realizes that it's actually the fastest method. So let's run his code and see what happens.

    RAISERROR('

    --==================================================================================================

    Luis Cazares

    (CROSSTAB for SQL Server 2000 - uses Temp Table for numbering)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#LotNo','U') IS NOT NULL

    DROP TABLE #LotNo

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

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

    LotNo

    INTO #LotNo

    FROM dbo.LotNo -- CHANGED THIS TO A TEMP TABLE

    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

    INTO #Target1 --ADDED THIS

    FROM #LotNo

    GROUP BY RowNo / 3

    SET STATISTICS TIME OFF

    ;

    GO

    ... and here are the results from his code.

    --==================================================================================================

    Luis Cazares

    (CROSSTAB for SQL Server 2000 - uses Temp Table for numbering)

    --==================================================================================================

    (1000000 row(s) affected)

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 1734 ms, elapsed time = 1732 ms.

    SQL Server parse and compile time:

    CPU time = 382 ms, elapsed time = 382 ms.

    (333334 row(s) affected)

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 3766 ms, elapsed time = 1187 ms.

    If we add up all of the CPU and Elapsed Times including the recompile the creation and population of the Temp Table force, here's what we end up with...

    CPU time = 5882 ms, elapsed time = 3301 ms.

    ... and just like Sergiy and I demonstrated years ago, that blows the doors off of any of the supposed modern methods of pivoting data using T-SQL.

    So, not only have we proven that PIVOT is challenged for performance when compared to a properly written "modern" CTE-based CROSSTAB but, if you set bit 137 of DBCC TIMEWARP and meet Mr. Peabody and Sherman in the "Wayback Machine" sometime prior to 2005, you'll find that the older CROSSTABs beat the tar out of everything.

    If you're still interested in other performance tests and how to make use of the CTE-based CROSSTABs even faster (for a more generic set of problems), please see the article that I originally posted. Here's the URL for that and the companion article on how to easily write dynamic CROSSTABs that are driven by the data.

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

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    --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)

  • Lol! That would be more of a challenge! But still doable - you would have to calculate page numbers. A high-volume scenario would be another special case. I would probably look to a report formatting engine or automate word with a multiple-column layout and let that mature, optimized code to the work.

  • Larry.Hennig (12/11/2015)


    Lol! That would be more of a challenge! But still doable - you would have to calculate page numbers. A high-volume scenario would be another special case. I would probably look to a report formatting engine or automate word with a multiple-column layout and let that mature, optimized code to the work.

    Heh... + 1000. Spot on with that! Still, it could be done if push came to shove. Thankfully, there's no pushing or shoving today! πŸ˜€

    --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)

  • x

  • Jeff Moden (12/11/2015)


    Ok... I realize that this is an older thread but people are still posting PIVOTs to do this and that's going to be slower than a good CROSSTAB. The problem is that the CROSSTABs that have been posted haven't been so hot and the best that's happened with all the code posted so far (well, except for the one that Larry.Hennig was kind enough to just make a posting for) is that the various CROSSTAB methods have mostly only tied the better PIVOT methods so far. As Scott Pletcher points out, part of the problem is that almost everyone has been using more than one ROW_NUMBER() and that's just not necessary.

    But, even with using just one ROW_NUMBER(), even Scott's good CROSSTAB code was only neck and neck with the fastest (they take turns winning) PIVOT code written by Clif.Johnson and pcanino70 .

    You can see all of this by running the following code, which creates a million row table similar to the original in the article (except I use GUIDs converted to VARCHAR to give it a little more bulk for testing and then runs almost all (saving one for later) of the various methods that actually accomplished the task cited in the original article for this discussion. It's all self-contained and runs in TempDB so we don't take a chance on dropping a real table just before building it.

    RAISERROR('

    --==================================================================================================

    Building the test data...

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== Do this in a nice, safe place that everyone has.

    USE tempdb

    ;

    --===== If the test table already exists, drop it to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb.dbo.LotNo','U') IS NOT NULL

    DROP TABLE dbo.LotNo

    ;

    --===== Create and populate the table on-the-fly.

    SELECT TOP 1000000

    IDNo = IDENTITY(INT,1,7) --Increment by 7 to simulate gaps in IDs

    , LotNo = CAST(NEWID() AS VARCHAR(50))

    INTO dbo.LotNo

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    ;

    RAISERROR('

    --==================================================================================================

    Baseline Test with just the straight data...

    This also "primes the pump" so that everyone has an equal chance.

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    SELECT Column1 = LotNo

    , Column2 = LotNo

    , Column3 = LotNo

    INTO #Target1

    FROM dbo.LotNo

    ;

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    Code from the article...

    (CROSSTAB - Multiple ROW_NUMBER())

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    with MstrTable ( LotNo, Remainder, ROWID )

    as ( select

    LotNo

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

    , case when ( row_number() over ( order by LotNo ) + 2 ) % 3 = 0

    then ( row_number() over ( order by LotNo ) + 2 )

    else case when ( row_number() over ( order by LotNo )

    + 2 ) % 3 = 1

    then ( row_number() over ( order by LotNo )

    + 1 )

    else ( row_number() over ( order by LotNo ) )

    end

    end as RowID

    from

    dbo.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

    INTO #Target1 --ADDED THIS

    from

    mstrtable

    group by

    ROWID

    ;

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    Kev Riley

    (CROSSTAB - 2 ROW_NUMBER() in FROM Clause)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    select --ADDED COLUMN NAMES

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

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

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

    INTO #Target1 --ADDED THIS

    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

    dbo.LotNo

    )a

    group by ROWID

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    Clif.Johnson-989960

    (PIVOT - 2 ROW_NUMBER() in FROM Clause)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

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

    INTO #Target1 --ADDED THIS

    FROM (

    SELECT LotNo

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

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

    FROM dbo.LotNo

    ) x

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

    ;

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    Luis Cazares (Option 1)

    (CROSSTAB - 2 ROW_NUMBER() in CTE)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    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 dbo.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

    INTO #Target1 --ADDED THIS

    FROM mstrtable

    GROUP BY ROWID;

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    Luis Cazares (Option 2)

    (CROSSTAB with NTILE() and ROW_NUMBER())

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    WITH Groups AS(

    SELECT LotNo

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

    FROM dbo.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

    INTO #Target1 --ADDED THIS

    FROM mstrtable

    GROUP BY ROWID;

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    ScottPletcher

    (CROSSTAB with single ROW_NUMBER() in FROM clause)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    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

    INTO #Target1 --ADDED THIS

    FROM (

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

    FROM dbo.LotNo

    ) AS derived

    GROUP BY row_num / 3

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    zbychbor

    (CROSSTAB - Multiple ROW_NUMBER() and 3 calls to the CTE using self joins)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    ;with lot(lotNo) as (

    select lotNo

    FROM dbo.LotNo

    )

    , cte02 as (

    select rn = row_number() over (order by lotNo)

    ,rn2 = 1 + (-1 + row_number() over (order by lotNo)) % 3

    ,rn3 = 1 + (-1 + row_number() over (order by lotNo)) / 3

    ,*

    from lot

    )

    , cte03 as (

    select rn = C1.rn

    , L1 = C1.lotNo

    , L2 = c2.lotNo

    , L3 = c3.lotNo

    from cte02 as C1

    left join cte02 as C2 on C1.rn3 = C2.rn3 and c2.rn2 = 2

    left join cte02 as C3 on C1.rn3 = C3.rn3 and c3.rn2 = 3

    where c1.rn2 = 1

    )

    select L1

    ,L2 = isnull(L2, '')

    ,L3 = isnull(L3, '')

    INTO #Target1 --ADDED THIS

    from cte03

    order by rn

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    pcanino70

    (PIVOT - 2 ROW_NUMBER() in CTE)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    with cte

    as

    (

    select LotNo

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

    , ( row_number() over ( order by LotNo ) - 1) / 3 as Category

    from dbo.LotNo

    )

    select Category, [0] as column1, [1] as column2, [2] as column3

    INTO #Target1 -- ADDED THIS

    from cte

    pivot

    (

    min(LotNo) for Remainder in ([0],[1],[2])

    ) as piv

    SET STATISTICS TIME OFF

    ;

    GO

    RAISERROR('

    --==================================================================================================

    Wilson.Mead

    (PIVOT)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    IF OBJECT_ID('tempdb..#ColumnNames','U') IS NOT NULL

    DROP TABLE #ColumnNames

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    CREATE TABLE #ColumnNames (ColumnKey int, Columnname VARCHAR(10))

    DELETE #ColumnNames

    INSERT INTO #ColumnNames (ColumnKey, ColumnName)

    Values (0, 'Column1')

    , (1, 'Column2')

    , (2, 'Column3')

    SELECT max(column1) Lot1, max(column2) Lot2, max(column3) Lot3

    INTO #Target1 --ADDED THIS

    FROM (

    SELECT Source.LotNo, source.ColumnKey, Source.RowKey, map.Columnname FROM (

    SELECT LotNo

    , (Row_Number() over (order by LotNo) + 2) / 3 as RowKey

    , (Row_Number() over (order by LotNo) - 1) % 3 as ColumnKey

    FROM dbo.LotNo

    ) Source

    INNER JOIN #ColumnNames Map on Map.ColumnKey = Source.ColumnKey %3

    ) AS ResultSet

    PIVOT (

    MAX (LotNo) FOR ColumnName in (Column1, Column2, Column3)

    ) as it

    group by RowKey

    SET STATISTICS TIME OFF

    ;

    GO

    Here are the run results that I was talking about. They don't show any particular advantage of CROSSTAB over pivot (yet).

    --==================================================================================================

    Building the test data...

    --==================================================================================================

    (1000000 row(s) affected)

    --==================================================================================================

    Baseline Test with just the straight data...

    This also "primes the pump" so that everyone has an equal chance.

    --==================================================================================================

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server Execution Times:

    CPU time = 985 ms, elapsed time = 987 ms.

    (1000000 row(s) affected)

    --==================================================================================================

    Code from the article...

    (CROSSTAB - Multiple ROW_NUMBER())

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 15125 ms, elapsed time = 5330 ms.

    (333334 row(s) affected)

    --==================================================================================================

    Kev Riley

    (CROSSTAB - 2 ROW_NUMBER() in FROM Clause)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 14435 ms, elapsed time = 4950 ms.

    (333334 row(s) affected)

    --==================================================================================================

    Clif.Johnson-989960

    (PIVOT - 2 ROW_NUMBER() in FROM Clause)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 13203 ms, elapsed time = 4625 ms.

    (333334 row(s) affected)

    --==================================================================================================

    Luis Cazares (Option 1)

    (CROSSTAB - 2 ROW_NUMBER() in CTE)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 14313 ms, elapsed time = 4758 ms.

    (333334 row(s) affected)

    --==================================================================================================

    Luis Cazares (Option 2)

    (CROSSTAB with NTILE() and ROW_NUMBER())

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 16218 ms, elapsed time = 10088 ms.

    (333334 row(s) affected)

    --==================================================================================================

    ScottPletcher

    (CROSSTAB with single ROW_NUMBER() in FROM clause)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 13501 ms, elapsed time = 4411 ms.

    (333334 row(s) affected)

    --==================================================================================================

    zbychbor

    (CROSSTAB - Multiple ROW_NUMBER() and 3 calls to the CTE using self joins)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 18828 ms, elapsed time = 19000 ms.

    (333334 row(s) affected)

    --==================================================================================================

    pcanino70

    (PIVOT - 2 ROW_NUMBER() in CTE)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 13578 ms, elapsed time = 4698 ms.

    (333334 row(s) affected)

    --==================================================================================================

    Wilson.Mead

    (PIVOT)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 15 ms, elapsed time = 1 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    (0 row(s) affected)

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    (3 row(s) affected)

    SQL Server parse and compile time:

    CPU time = 16 ms, elapsed time = 16 ms.

    SQL Server Execution Times:

    CPU time = 18062 ms, elapsed time = 5708 ms.

    Warning: Null value is eliminated by an aggregate or other SET operation.

    (333334 row(s) affected)

    [font="Arial Black"]A Faster CROSSTAB[/font]

    Like I said, the results don't show any particular advantage of CROSSTAB over PIVOT and yet I'm still going to insist that CROSSTAB is about 38% faster and uses about 36% less CPU time than PIVOTs. Run the following code and see the results there.

    RAISERROR('

    --==================================================================================================

    Jeff Moden

    (CROSSTAB - 1 ROW_NUMBER in CTE)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

    WITH

    cteEnumerate AS

    (

    SELECT RowNum = ROW_NUMBER() OVER (ORDER BY IDNo)-1

    ,LotNo

    FROM dbo.LotNo

    )

    SELECT Column1 = MAX(CASE WHEN RowNum%3 = 0 THEN LotNo ELSE '' END)

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

    , Column3 = MAX(CASE WHEN RowNum%3 = 2 THEN LotNo ELSE '' END)

    INTO #Target1

    FROM cteEnumerate

    GROUP BY RowNum/3

    ;

    SET STATISTICS TIME OFF

    ;

    GO

    Here are the results from the above.

    --==================================================================================================

    Jeff Moden

    (CROSSTAB - 1 ROW_NUMBER in CTE)

    --==================================================================================================

    SQL Server Execution Times:

    CPU time = 9657 ms, elapsed time = 3345 ms.

    (333334 row(s) affected)

    If you check the Actual Execution Plans between Scott's CROSSTAB with the ROW_NUMBER() in the FROM clause and mine with the ROW_NUMBER() in the CTE, you'll only see minor differences between them. Someone like Grant Fritchey could explain it much better but what I saw was that the optimizer over estimated the size of the rows for Scott's code. I've not done a deep enough dive to explain better than that but the CTE version also always came out the fastest in the article I posted earlier in this thread.

    Can we make the PIVOT run a bit faster by limiting it to only a single ROW_NUMBER()? I'm thinking "NOT" but I welcome someone to try and report the test results compared to the CTE CROSSTAB that I wrote above.

    [font="Arial Black"]And now... the rest of the story.[/font]

    Now, I told you that story to tell you this one. A long time ago when 2005 first came out, a good friend of mine who goes by the handle of "Sergiy" on this forum and I took on all comers for similar testing except we beat the tar out of other folks methods that did use ROW_NUMBER() by using much older tricks that most have forgotten by now. Luis Cazares didn't forget the method when someone asked how to solve this problem in SQL Server 2000 where ROW_NUMBER() didn't yet exist but I'm not sure that he realizes that it's actually the fastest method. So let's run his code and see what happens.

    RAISERROR('

    --==================================================================================================

    Luis Cazares

    (CROSSTAB for SQL Server 2000 - uses Temp Table for numbering)

    --=================================================================================================='

    ,0,0) WITH NOWAIT

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Target1','U') IS NOT NULL

    DROP TABLE #Target1

    ;

    --===== If the Temp Tables already exist, drop them to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#LotNo','U') IS NOT NULL

    DROP TABLE #LotNo

    ;

    --===== Conduct the test

    SET STATISTICS TIME ON

    ;

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

    LotNo

    INTO #LotNo

    FROM dbo.LotNo -- CHANGED THIS TO A TEMP TABLE

    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

    INTO #Target1 --ADDED THIS

    FROM #LotNo

    GROUP BY RowNo / 3

    SET STATISTICS TIME OFF

    ;

    GO

    ... and here are the results from his code.

    --==================================================================================================

    Luis Cazares

    (CROSSTAB for SQL Server 2000 - uses Temp Table for numbering)

    --==================================================================================================

    (1000000 row(s) affected)

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 1734 ms, elapsed time = 1732 ms.

    SQL Server parse and compile time:

    CPU time = 382 ms, elapsed time = 382 ms.

    (333334 row(s) affected)

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 3766 ms, elapsed time = 1187 ms.

    If we add up all of the CPU and Elapsed Times including the recompile the creation and population of the Temp Table force, here's what we end up with...

    CPU time = 5882 ms, elapsed time = 3301 ms.

    ... and just like Sergiy and I demonstrated years ago, that blows the doors off of any of the supposed modern methods of pivoting data using T-SQL.

    So, not only have we proven that PIVOT is challenged for performance when compared to a properly written "modern" CTE-based CROSSTAB but, if you set bit 137 of DBCC TIMEWARP and meet Mr. Peabody and Sherman in the "Wayback Machine" sometime prior to 2005, you'll find that the older CROSSTABs beat the tar out of everything.

    If you're still interested in other performance tests and how to make use of the CTE-based CROSSTABs even faster (for a more generic set of problems), please see the article that I originally posted. Here's the URL for that and the companion article on how to easily write dynamic CROSSTABs that are driven by the data.

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

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    Well done! Like the results. The one thing I like about the pivot is for me it allows easy expansion if you need to fatten this out more.

  • pcanino70 (12/13/2015)


    Well done! Like the results. The one thing I like about the pivot is for me it allows easy expansion if you need to fatten this out more.

    Thanks for the feedback but let me ask you a question... why do you think doing that same thing with a CROSSTAB is difficult?

    --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)

  • Then I suggest, Jeff, you present your solution for an unknown ahead of time number of columns, which may be different between records. The simplest way I could find to do it was with dynamic SQL temp table, scaning the table once to determine the columns, once to determine the rows, and a final update to fill in the values,

  • Jeff Moden (12/13/2015)


    pcanino70 (12/13/2015)


    Well done! Like the results. The one thing I like about the pivot is for me it allows easy expansion if you need to fatten this out more.

    Thanks for the feedback but let me ask you a question... why do you think doing that same thing with a CROSSTAB is difficult?

    For this example I think you are right on the mark and the cross tab is defiantly the correct approach and I’m rethinking some of my queries, my concern was on some more complex queries that I haven’t had a chance to evaluate to see if there is a benefit of pivot over cross tabs. Excellent post, thank you very much for your insight.

  • Thanks for the article. Quite a number of good alternatives in the comments too.

  • I did some further analysis on Jeff Moden's example and I'm convinced his cross tab method is the proper way to go, I just converted a query that is almost 40% faster to execute with80% less CPU time. Well done sir, you have changed my approach going forward...

  • Here is simple way of getting the output.

    declare @LotNo table (id int identity(1,1),LotNo varchar(50) Null)

    insert into @LotNo

    select 'A'

    union all

    select 'B'

    union all

    Select 'C'

    union all

    select 'D'

    union all

    Select 'E'

    union all

    select 'F'

    union all

    Select 'G'

    union all

    select 'H'

    union all

    Select 'I'

    union all

    select 'J'

    union all

    Select 'K'

    union all

    select 'L'

    union all

    Select 'M'

    union all

    select 'N'

    union all

    Select 'O'

    union all

    select 'P'

    union all

    Select 'Q'

    union all

    select 'R'

    union all

    Select 'S'

    union all

    select 'T'

    union all

    Select 'U'

    union all

    select 'V'

    union all

    Select 'W'

    union all

    select 'X'

    union all

    Select 'Y'

    union all

    select 'Z'

    select col1,col2,col3 from

    (

    select Id,LotNo col1,

    lead(LotNo,1,'') over (order by LotNo) col2,

    lead(LotNo,2,'') over (order by LotNo) col3

    from @LotNo

    )a

    where (id%3)=1

    Regards

    Siva πŸ™‚

  • sivaj2k (12/16/2015)


    Here is simple way of getting the output.

    ...

    Regards

    Siva πŸ™‚

    Only if you can be sure that there won't be any gaps. In the real world, that's unlikely to happen.

    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
  • pcanino70 (12/14/2015)


    I did some further analysis on Jeff Moden's example and I'm convinced his cross tab method is the proper way to go, I just converted a query that is almost 40% faster to execute with80% less CPU time. Well done sir, you have changed my approach going forward...

    Well done to you! A lot of people won't go back to things that are working to try to make them better. And, thank you very much for the feedback.

    --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 14 posts - 46 through 59 (of 59 total)

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