Unusual SQL Dataset I Can't Figure out How to Use

• 

  [Deactivated] on 09/12/2017 - 22:12

  -3

  Yes, ask about SQL on a website made for finding bargains…

  Because you know… Google is too hard, so can't find a more relevant website… Like… Oooohhh I dunno…. https://stackoverflow.com

• 

  Xyzzy on 09/12/2017 - 22:15

  +1

  Are you sure all company code's are 1001? The data would be usable with a unique identifier of the vendor, but without one it's difficult.

  • 

    gmail92 on 09/12/2017 - 22:18

    Yes, I was expecting a primary key of some sort - but none exist.

    • 

      Xyzzy on 09/12/2017 - 22:29

      +1

      You can translate the rows into "Vendor Name" and "Vendor Address" columns with CASE statements e.g. something like:

      SELECT "Company Code",
      max(CASE WHEN Field = 'Vendor Name' THEN Value END) AS Vendor_Name,
      max(CASE WHEN Field = 'Vendor Address' THEN Value END) as Vendor_Address
      from table
      GROUP BY "Company Code"

      but that would only work if company code was unique per vendor. Without a unique key I can't see what you can do with the data.

• 

  tsunamisurfer on 09/12/2017 - 22:27

  +1

  Is there something else? How is one to know which address applies to which company?

• 

  mja65 on 09/12/2017 - 23:15

  +1

  Looks like this data came from SAP and I am guessing from the SAP vendor change report. Agreed with Xyzzy - it needs a vendor number. Whoever gave you this data neglected to provide that.

  I think once you have that and assuming this is MS SQL you could use the SQL pivot command:

  https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

  Just use google as Drew22 suggested.

  Let me guess - looking for a job working in audit analytics?

  • 

    gmail92 on 10/12/2017 - 16:16

    Thanks for the suggestions guys, I have concluded it is a broken table. You are right though - assuming it was supposed to be that way - it could look like CDHDR or CDPOS.

    Assuming each field had a primary key, I would have been able to recompose the table. It was actually an Employee Master file, but I simplified it for the explanation.

    Already in Audit Analytics, but I don't personally use SQL for it.

    • 

      [Deactivated] on 10/12/2017 - 18:25

      Do you use something like Idea or ACL?

      • 

        gmail92 on 11/12/2017 - 14:23

        Yes

      • 

        [Deactivated] on 12/12/2017 - 12:21

        @gmail92:
        Which one?

      • 

        gmail92 on 12/12/2017 - 13:50

        @Drew22:

        ACL

• 

  D C on 10/12/2017 - 10:22

  +1

  That's EAV (Entity, Attribute, Value) format; as mentioned you could use PIVOT to get to what you'd prefer but someone has cocked up the 'Entity' column.

  Send it back and ask for a refund.

  Complain to the ACCC as well, which seems to be a popular theme here at the moment for minor grievances.

  • 

    [Deactivated] on 12/12/2017 - 12:22

    -1

    You forgot about the bikies.