Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Posts
    115

    Unanswered: converting audit data to create history

    Hi

    I have a table called my_history that has columns like this

    Code:
    column_name ,     old_value,          new_value,            key,          date
    bankaccount         30                     50                       1            01-Apr-2008
    bankbalance          10                     14                       2            04-Apr-2008
    and so on............

    The history table is populated using triggers

    The main table called my has a structure like

    Code:
    bankaccount         bankbalance           bankname             name          address             key
    50                       50                        xyz                      abc            ford                  1
    30                       14                        abc                      xyz            east                  2
    Now using this information can i reconstruct the records in my table before the update happened ?

    I am finding it very difficult to do this is there a way to do this in t-sql ?

    The problem is my_history table where the column_name keeps on varrying

    regards
    Hrishy
    Last edited by db2hrishy; 04-09-08 at 05:41.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you want to PIVOT your data. In SQL2000, you can do this using CASE statements, in 2005 there is a new PIVOT operator.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2004
    Posts
    115
    Hi

    I am on sqlserver 2005 and i tried the pivot operator however even with that i am unable to construct the full row as the number of columns are just too many..

    regards
    Hrishy

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    admittedly I am not too familiar with the pivot operator. I do a lot of backend processing lately and I rarely have to worry about presentation.

    do you want to post what you have? Have you tried the CASE statement approach.

    There is a lot of good reading here...

    http://www.google.com/search?hl=en&q...ss-Tab+Reports
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    i ran into a limitation on using the PIVOT operator for creating very wide crosstabs - about 1000 columns - don't ask .

    I ended up writing my own app to do it as PIVOT and the CASE methods can't seem to handle such wide crosstabs. rightly so I would say as such reports are an abomination.

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97164

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I just wanna see how you present a crosstab with that many columns!

    -PatP

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    it was a direct request from a customer that wanted to "get a feel for the data in excel". output is to a csv.

    The whole dataset was about 1 billion rows - apparently this customer thinks it's neat to open up a crosstab in excel with 1 million rows and 1000 columns instead of loading 1 billion rows, 3 columns each into a proper DBMS.

    I tried to convince the PM it was idiotic, but lost. I'm getting steamed again just thinking about it...

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, I'm gonna just chalk this request up to "copious quantities of recreational pharmaceuticals" instead of trying to make sense of it. My mind can't even grok that request, so there's no point in contemplating the output. I'd be looking for whoever submitted/approved the request with a stick long before I got done coding any kind of solution!

    As Sallah said to Indy: "Asps... Very dangerous... You go first."

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •