Convert Rows into Columns

  • Thanks Jeff - I did CROSSTAB implementation before SQL 2005.

    For my current project I stick with PIVOT (I have less than 10K rows). 😉

  • Even if Jeff posted his great article, I wanted to post this thread where I compared the performance in 2 different scenarios.

    http://www.sqlservercentral.com/Forums/Topic1537999-392-1.aspx

    You could try it yourself and compare results. 😉

    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
  • Thanks Luis, I hate to bring another dimension to performance test but I am using TABLE variable, not temporary table. 😉

  • fregatepllada (7/17/2014)


    Thanks Jeff - I did CROSSTAB implementation before SQL 2005.

    For my current project I stick with PIVOT (I have less than 10K rows). 😉

    Why would you intentionally use slower code just because of a small rowcount? It's like hitting wrong notes on the piano just because no one is looking. 😉

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

  • fregatepllada (7/17/2014)


    Thanks Luis, I hate to bring another dimension to performance test but I am using TABLE variable, not temporary table. 😉

    Heh... let me guess... "small row count". 😀

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

  • Despite all of my protests I have to implement EAV in my current project because some genius :alien: "discovered" this anti-pattern. I do not need to do any aggregation and number of columns after PIVOT transformation could not exceed 4096 columns 🙂

  • I dislike the solution proposed, it's far too complicated to reach a so easy task.

    You requirement was to print 3 lots at a time per line of document. This mean that you should have a table with lot associated with the Line, a simple left join will suffice.

    If your table looks like this

    IDNo LotNo SaleOrdLine

    1 A 1

    2 B 1

    3 C 1

    4 D 2

    5 E 2

    6 F 2

    7 G 2

    8 H 2

    9 I 2

    10 J 2

    11 K 3

    12 L 3

    13 M 3

    14 N 3

    15 O 3

    16 P 3

    17 Q 3

    18 R 3

    19 S 3

    20 T 3

    21 U 3

    22 V 3

    23 W 3

    24 X 3

    25 Y 3

    26 Z 3

    select L1.SaleOrdLine,L1.LotNo as L1,coalesce(L2.LotNo,'') as L2,coalesce(L3.LotNo,'') as L3

    from [LotNo] L1

    left join [LotNo] L2 on L1.saleOrdLine=L2.SaleOrdLine and L2.idno=L1.IDNo+1

    left join [LotNo] L3 on L2.saleOrdLine=L3.SaleOrdLine and L3.idno=L2.IDNo+1

    where L1.IDNo%3=1

    with this results:

    SaleOrdLine L1 L2 L3

    1 A B C

    2 D E F

    2 G H I

    2 J

    3 M N O

    3 P Q R

    3 S T U

    3 V W X

    3 Y Z

    Best regards,

    PS: your solution is indeed good, but for more complex applications.

  • And next solution:

    regards

    Zbig

    ;with lot(lotNo) as (

    select lotNo

    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)

    )

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

    from cte03

    order by rn

  • fregatepllada (7/17/2014)


    Despite all of my protests I have to implement EAV in my current project because some genius :alien: "discovered" this anti-pattern. I do not need to do any aggregation and number of columns after PIVOT transformation could not exceed 4096 columns 🙂

    What's that got to do with whether or not you uses a Table Variable? :blink:

    Heh... and contrary to what many "experts" say, EAVs and NVPs have their place and are quite useful. EAVs are not, in and of themselves, an "anti-pattern". In fact, many types of properly-normalized tables are actually EAVs or NVPs under the hood. Think about a checkbook-like table for many different accounts like a bank would have... it has a primary key, dollar amounts, dates, an "entity" (the account number), and an attribute (deposit, automatic deposit, refunds, ATM withdrawal, check, credit card, interest, a multitude of service charges, etc, ad infinitum). 😉

    If you did a pivot using either a CROSSTAB or a PIVOT, no matter if it's on numbers or text, then you used an aggregation f one form or another (MAX, for example, is an aggregation) If you used multiple self joins on the table to pivot the data instead, there's very strong likelihood that you've done it wrong. If your table is constructed to hold multiple columns of identical type data based on some criteria such as date or location, then your table doesn't follow the rules of normalization and may actually be an "anti-pattern".

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

  • antonio.zerbinati (7/18/2014)


    I dislike the solution proposed, it's far too complicated to reach a so easy task.

    You requirement was to print 3 lots at a time per line of document. This mean that you should have a table with lot associated with the Line, a simple left join will suffice.

    Your solution requires a table with a column of contiguous sequential numbers, which is no guarantee in real life unless you build the sequential numbers at run time.

    You're also hitting the table 3 times and, while that may be appropriate in this case because the table needs to be in memory to be read even just once, there's usually more work involved (and, so, more resources used) behind the scenes to involve 3 instances of the same table using calculated self joins. While the code for a CROSS APPLY or PIVOT may be a bit longer, it can be much more effective than using calculated self joins (requires 3 full table scans, in this case).

    I do, however, agree that the code in the article is quite complex for the task at hand, though. One of the single ROW_NUMBER solutions with the integer division methods for the pivot (whichever method you choose) will likely be the all-around best method for this simple task.

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

  • zbychbor (7/18/2014)


    And next solution:

    regards

    Zbig

    ;with lot(lotNo) as (

    select lotNo

    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)

    )

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

    from cte03

    order by rn

    As I said in my previous post above, multiple self-joins is a pretty expensive way of doing this. Compound that with the multiple ROW_NUMBER and related sorts they produce and things can slow down quite a bit. Consider using one of the single ROW_NUMBER/single table pass pivoting solutions, instead.

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

  • You're usage of row_number(), modulo and such is impressive, but I

    must admit that I found the solution a bit over complex and it was making

    my head hurt a bit.

    Consider the following code, the key is using the COALESCE statement, the

    example assumes using the original 'LOTNO' table populated in the post:

    DECLARE @returnResult varchar(255)

    , @StartLotNumber int = 0

    , @LastLotNumber int = 13

    SELECT @returnResult = COALESCE(@returnResult + ',', '') + lot.LotNo, @LastLotNumber = IDNo

    FROM dbo.LotNo lot

    WHERE IDNo BETWEEN @StartLotNumber AND @LastLotNumber

    SELECT 'Lots for Line Item#1: ' + @returnResult

    select @StartLotNumber = @LastLotNumber + 1, @LastLotNumber = @StartLotNumber + 13, @returnResult = ''

    SELECT @returnResult = COALESCE(@returnResult + ',', '') + lot.LotNo, @LastLotNumber = IDNo

    FROM dbo.LotNo lot

    WHERE IDNo BETWEEN @StartLotNumber AND @LastLotNumber

    SELECT 'Lots for Line Item#2: ' + @returnResult

    OUTPUT RESULTS

    Lots for Line Item#1: A,B,C,D,E,F,G,H,I,J,K,L,M

    Lots for Line Item#2: ,N,O,P,Q,R,S,T,U,V,W,X,Y,Z

  • Hello Guys

    Really seem like a very long way to do what pivot and unpivot do, or am I missing the point here?

  • sleon (7/21/2014)


    Hello Guys

    Really seem like a very long way to do what pivot and unpivot do, or am I missing the point here?

    You still need the groups and column identifiers. Read the rest of the posts to see that different methods including pivot have been posted.

    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
  • Hello,

    I already worked on this. But i used another tool which is Stata to convert both column to rows and rows to column and i used TOS to insert Data in DB. It is great and easy.

Viewing 15 posts - 16 through 30 (of 59 total)

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