How to rotate a table from horizontal to vertical [DB]

Discussion in 'Programming & Software Development' started by glasnt, Mar 16, 2009.

  1. glasnt

    glasnt Member

    Joined:
    Aug 9, 2004
    Messages:
    3,793
    It's easy to change a bunch of rows into columns using case statements, like with quarter sales information, but I want to try to work out how to efficently change a table with an indeterminate amount of rows into a temp_table of lots of rows and minimal columns.


    Basically I want to take two rows of data, e.g.

    Code:
    1 2 3 4 5 6 7 8 9 0
    A B C D E F G H I J 
    and turn it into
    Code:
    1 A
    2 B
    3 C etc..
    However, I'd also like to be able to take

    Code:
    1 2 3 4 5 6 7 8 9 0
    A B C D E F G H I J
    K L M N O P Q R S T
    and turn it into
    Code:
    1 A K
    2 B L
    3 C M etc..
    This is so I can then take each row of my new dataset and perform equivalence checks on them. I figure that it's easier to loop for all rows in a select statement than it is to loop through columns in a row, as I can't work out how to loop columns, especially as I don't want to declare the columns, because there is up to 60 I have to deal with.

    Any thoughts?


    edit: alternatively, is there a way to loop through columns using sybase? I could nearly do it in oracle, but that's using pl/sql which there is no equivalent language for in sybase.
     
    Last edited: Mar 16, 2009
  2. purehybrid

    purehybrid Member

    Joined:
    Sep 24, 2004
    Messages:
    4,549
    Location:
    Melbourne
    If it isn't something that you need to do a lot, and therefore doesn't require automation... just whack it into excell, use the paste special - transpose function, and reimport it.

    Unfortunately, due to time constraints this sort of dodgy work around is pretty standard.

    If it does need to be automated, hopefully someone here with a bit more industry experience can help you out :)
     
  3. OP
    OP
    glasnt

    glasnt Member

    Joined:
    Aug 9, 2004
    Messages:
    3,793
    Unfortunately, it needs to be dynamic, as part of a procedure that will end up generating the required data for an on the fly report.

    Even in Excel it would take a while to edit all the columns into rows :p
     
  4. Parker

    Parker Member

    Joined:
    Jul 8, 2002
    Messages:
    101
    Location:
    Wagga
    The only time Ive had to do something like this was in PHP.
    In that case I built a multidimensional array by looping through the data.
    That array was then constructed in the 'correct' format to allow me to loop through the array content in a foreach statement.
     
  5. OP
    OP
    glasnt

    glasnt Member

    Joined:
    Aug 9, 2004
    Messages:
    3,793
    So far i've worked out I can actually aggragate a list of the columns I need from the table by going

    Code:
    SELECT c.name ColumnName
    FROM syscolumns c, sysobjects o where c.id = o.id 
    and o.name = 'myTable'
    and c.name not like 'audit[_]%'
    order by c.name asc
    That outputs all the columns of the name that aren't prefixed by 'audit_'.

    Now, if I can get that list of columns and then aggregate a bunch of dynamic sql around that, I might be ok.



    edit: even if I could get a string aggragator working..

    at the moment I could aggregate on-the-fly a complex select query based on

    Code:
    (case 
      when convert(varchar(12),column_here) is null 
        then null 
      else convert(varchar(12),column_here) + char(13) 
    end)
    but in large numbers that would be very inefficient.
     
    Last edited: Mar 16, 2009
  6. Luke212

    Luke212 Member

    Joined:
    Feb 26, 2003
    Messages:
    10,058
    Location:
    Sydney
    some databases have a pivot function for converting row data to columns. there is also an unpivot function for converting column data to rows.
     
  7. OP
    OP
    glasnt

    glasnt Member

    Joined:
    Aug 9, 2004
    Messages:
    3,793
    From a sybase guy:

    "Thanks for your interest in PIVOT (and UNPIVOT). We do not currently have
    plans to implement these two operators at this point. However, we are well
    aware that PIVOT and UNPIVOT can be very useful operations, particularly in
    an OLAP context, and we will be evaluating these and other OLAP
    improvements for a forthcoming release of SQL Anywhere."
    source: http://bytes.com/community/misc/720374-equivalent-transform-pivot-sybase



    edit: I've worked out so far that you can't nest cursors in sybase because of inefficiency issues. I only got about 124 loops of 100 inner loops and my IDE crashed. Fun fun.
     
    Last edited: Mar 16, 2009
  8. Ohmigosh

    Ohmigosh Member

    Joined:
    Jun 28, 2001
    Messages:
    570
    Location:
    Sydney
    (Disclaimer - I haven't touched SYBASE since Uni back in the late eightees (At least I think it was SYBASE))

    Can you get hold of some DataMart software that will allow you to access PIVOT style functionality?

    You could then simply copy the data into the DataMart and execute your pivot there. :thumbup:

    I reckon you'll just go bald with pulling your hair out trying to get an elegant, maintainable PIVOT out of PL-SQL. :thumbdn:

    *pats his nice new SQL Server 2008 installation* :D
     
  9. OP
    OP
    glasnt

    glasnt Member

    Joined:
    Aug 9, 2004
    Messages:
    3,793
    I can't use PL/SQL in Sybase, I can only use T-SQL, but it's a similar thing, but I still can't really get the hang of it.

    For now I've been able to isolate about 30% of the columns that are actually useful to the user, and just hardcoded those in.

    I'm about a third of the way through this problem, but I'll see if my co-workers have any ideas about this one.
     
  10. Luke212

    Luke212 Member

    Joined:
    Feb 26, 2003
    Messages:
    10,058
    Location:
    Sydney
    glasnt, maybe you could educate me on equivalence checking, and why you do it?
     
  11. OP
    OP
    glasnt

    glasnt Member

    Joined:
    Aug 9, 2004
    Messages:
    3,793
    Basically we have an audit table that records the current version of a row from another table when it is created, updated, or removed. So over time you get many records of how a certain row looked in the past.

    E.g.

    1 John Smith 123 456 I
    1 John Jones 123 456 U
    1 John Jones 123 890 U

    The requirement is to report all [create/delete/update] records for a given time period, given other parameters.

    So, if I want to show all changes every for record #1, I will produce

    Code:
    Action: CREATE
               Before     After
    Name:                 John Smith
    Col 1:                123
    Col 2:                456
    
    Action: CREATE
               Before     After
    Name:   John Smith   John Jones
    
    Action: CREATE
               Before     After
    Col 2:      456       890
    My problem is that these Col1, Col2 are actually up to Col97 and I didn't want to have to declare all the columns in everything I was doing. However, I've worked out that I need to report on only some 40 of the 100ish columns, so it's a bit easier. It still looks a mess when it takes a block of code 8 lines deep to just pull a row from a table and store into individual parameters.
     
  12. Ohmigosh

    Ohmigosh Member

    Joined:
    Jun 28, 2001
    Messages:
    570
    Location:
    Sydney
    Heh! I meant T-SQL :p

    I think your example should say UPDATE for the last two bits...
    Code:
    Action: CREATE
               Before     After
    Name:                 John Smith
    Col 1:                123
    Col 2:                456
    
    Action: [B]UPDATE[/B]
               Before     After
    Name:   John Smith   John Jones
    
    Action: [B]UPDATE[/B]
               Before     After
    Col 2:      456       890
    So does your audit table look something like this?

    Code:
    AuditPK OriginalDataKey  FieldUpdated TxDate   Before       After         TxType
    1       1234             Name         1/1/09   [null]       John Smith    I
    2       1234             Col1         1/1/09   [Null]       123           I
    3       1234             Col2         1/1/09   [Null]       456           I
    4       1234             Name         2/1/09   John Smith   John Jones    U
    5       1234             Col2         3/1/09   459          890           U
    If so, then you don't need to pivot the table.

    You could just do something like this...

    Code:
      SELECT  OriginalDataKey, TxDate, FieldUpdated, TxType, Before, After
        FROM  AuditTable
    ORDER BY  OriginalDataKey, TxDate, FieldUpdated, TxType
    This will display for each audited record a history of changes sorted by date and the table fields and whether its a Insert, Update, Delete.

    If you want to get even more funky you can force the order to be I then U then D by inserting a CASE in place of the TxType order by field.

    Something like...

    Code:
    ORDER BY OriginalDataKey, TxDate, FieldUpdated, 
            CASE TxType
                WHEN "I" THEN 1
                WHEN "U" THEN 2            
                ELSE 3
            END
    And you could build your report so that it has three sub-sections. One for the INSERT, a second for the UPDATE and third for the DELETE.

    'Course I could be talking out my arse! :D
     
  13. OP
    OP
    glasnt

    glasnt Member

    Joined:
    Aug 9, 2004
    Messages:
    3,793
    The table I'm deriving this information from is formatted like:


    1 John Smith 123 456 I
    1 John Jones 123 456 U
    1 John Jones 123 890 U

    Where the columns are ID, Name, COL1, COL2, ACTION

    I need to derive the pretty.
     

Share This Page

Advertisement: