Results 1 to 8 of 8
  1. #1
    Join Date
    May 2013
    Posts
    4

    Unanswered: Dynamic query to flatten data

    I have a query in SQL that I would like to use on my linked Oracle table but I get multiple errors when I try to use it.

    Basically what I want to do is take a table where there are multiple entries for a student and concatenate those values into one column separated by commas. This is the data example:

    Student Name Effective Term Major
    Jane Smith 20033 Geog
    Jane Smith 20033 Soc
    John Smith 20043 Comp
    John Smith 20043 Geog

    This is what I want:
    Jane Smith 20033 Geog,Soc
    John Smith 20043 Comp,Geog

    We are using Oracle 11g. Can anyone tell me if this can be done? I can get a pivot query to work but I need the values to be dynamic for Major because there are over 500 codes for Major.

    Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It can be done.
    You can also poke yourself in the eye with a sharp pencil.
    I suggest that neither action should ever be actually done.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2013
    Posts
    4
    I don't recall asking if it "should" be done. I don't think that is any of your concern and if you are so smart why didn't you just tell me the answer. I didn't sign up here for abuse. I actually found the answer already and it just happens that it is used quite often.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'll second anacedent's observation that this is a really bad idea, but it can be done.

    Do you have a limit on how many majors someone can have, or could one person potentially have every major that you offer?

    The choices on how to present and manage the return set will be influenced significantly by the tool or tools that can be used to display that result. Some choices work well in one environment but very poorly in others. Can you offer some insight on how you intend to use this result so that we can suggest one of the ways better suited to your usage?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    May 2013
    Posts
    4
    As I stated above. I already have this figured out and I don't need any more comments about how it is a bad idea. It was just a one time pull of data and they did not want duplicate entries. And yes, I am smart enough to limit the data. There were only a max of 3 majors that ended up in the field for the query I was looking at. I just didn't want duplicate rows.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You asked if it could be done & I responded that yes it was possible.
    Storing multiple values in a single column is consider flawed and not normalized by most DB professionals.

    > I didn't sign up here for abuse.
    If you considered my response to be abusive, consider getting therapy for your persecution complex.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    May 2013
    Posts
    4
    Wow. You are an idiot! I am not storing the data anywhere. It was just a query to put the data down.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by kit5963 View Post
    Wow. You are an idiot! I am not storing the data anywhere. It was just a query to put the data down.
    DB tables are comprised of multiple columns.
    Below is from your original post!

    >concatenate those values into one column separated by commas.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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