Thread: Data Help

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2010
    Posts
    3
    Rep Power
    0

    Data Help


    New guy here looking for a little SQL help.

    My data currently looks like this.

    UniqueId;Index Name;Index Value
    850318a4a66641b0;Doc ID;098765
    850318a4a66641b0;SSN;111-11-1111

    1c6def8069034b4b;Doc ID;456788
    1c6def8069034b4b;SSN;566-65-6510

    fdc43d6280934572;Doc ID;876543
    fdc43d6280934572;SSN;123-23-0000

    41b2d0ac89a34214;Doc ID;444567
    41b2d0ac89a34214;SSN;000-99-8888



    Is there an easy way to make it look like this or something like this?

    UniqueId;Index Name1;Index Value1;Index Name1;Index Value1
    850318a4a66641b0;Doc ID;098765;SSN;111-11-1111
    1c6def8069034b4b;Doc ID;456788;SSN;566-65-6510
    fdc43d6280934572;Doc ID;876543;SSN;123-23-0000
    41b2d0ac89a34214;Doc ID;444567;SSN;000-99-8888

    Thanks for any advice
    Dan
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    19
    Rep Power
    0
    It's hard to tell without looking at your database setup. If you could provide more information about the table then we can help you further.

    Is this the result of a query? Are you purposely trying to concatinate the string or can you take a tabular format so you have:

    Code:
    UniqueID     DocID     SSN
    1            445       123456789
    2            446       234567891
    3            447       345678912
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2010
    Posts
    3
    Rep Power
    0
    Originally Posted by Eturnal
    It's hard to tell without looking at your database setup. If you could provide more information about the table then we can help you further.

    Is this the result of a query? Are you purposely trying to concatinate the string or can you take a tabular format so you have:

    Code:
    UniqueID     DocID     SSN
    1            445       123456789
    2            446       234567891
    3            447       345678912

    Eturnal-
    You are correct this is the way I would need the data. Tabular format would work fine.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    19
    Rep Power
    0
    Assuming all the data is in the same table, you would simply run a SELECT against that table.

    SELECT t.UniqueID, t.DocID, t.SSN FROM table AS t

    That would give you all the records from the table without any constraints. If the information you're trying to pull is spread across multiple tables, then you would need to do a join of all tables involved.

    If you could tell us what tables have this information and list the fields associated with the table, we can help you construct your query.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by Eturnal
    Assuming all the data is in the same table, you would simply run a SELECT against that table.
    but it ~isn't~ a simple SELECT against the table

    take a closer look at his table --

    CREATE TABLE horribilis
    ( UniqueId VARCHAR(37)
    , IndexName VARCHAR(37)
    , IndexValue VARCHAR(37)
    );

    this is a classic entity-attribute-value (EAV) scheme

    do a search on that phrase and you will see how many other poor souls have been dashed to bits on the cruel reality of complex data retrieval after being tempted by the deceptively simple EAV data storage design

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2010
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    but it ~isn't~ a simple SELECT against the table

    take a closer look at his table --

    CREATE TABLE horribilis
    ( UniqueId VARCHAR(37)
    , IndexName VARCHAR(37)
    , IndexValue VARCHAR(37)
    );

    this is a classic entity-attribute-value (EAV) scheme

    do a search on that phrase and you will see how many other poor souls have been dashed to bits on the cruel reality of complex data retrieval after being tempted by the deceptively simple EAV data storage design

    ok i have this part do i need to have a select into also? sorry very new at this
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by dmcdevitt1234
    sorry very new at this
    that's okay

    the best advice i can give you is to redesign your table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo