Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2008
    Posts
    4

    Unanswered: Concentrating Many Records into One Field

    Please help. I am somewhat new to MS SQL programming and need all of the help I can get. I have search for other posts on this subject but I am confused by the answers and unsure how they could be applied to what I am trying to do.

    I have a table (tblIntakeMain) with a PK (IntakeMainID). I have a second table (tblPersonnel) with a PK (PersonnelID) and a foreign key field related to tblIntakeMain called fk_IntakeMainID. This is a one to many relationship (meaning there are anywhere from one to 20 records in tblPersonnel related to one record in tblIntakeMain).

    Within tblPersonnel, I have field called FullName. I would like to be able to concentrate using fk_IntakeMainID, all of the FullName fields into on record with each FullName record separated by a ",". I beliebe a view would work best for this (called vw_PersonnelFullNames).

    tblPersonnel currently has over 1000+ records and grows by 20 or 30 per day so the view would need to be dynamic, meaning that as new records are added the view is updated.

    So, just as an example:

    If tblPersonnel had the following data:

    Code:
    PersonnelID        fk_IntakeMainID        FullName
    1                      1                           Bob Smith
    2                      1                           Dow Jones
    3                      2                           Roberta Smith
    4                      1                           John Dow
    5                      3                           Perry Kerr
    6                      3                           Jen Chow
    Result would be:

    vw_PersonnelFullNames:

    Code:
    fk_IntakeMainID        FullName
    1                           Bob Smith, Dow Jones, John Dow
    2                           Roberta Smith
    3                           Perry Kerr, Jen Chow
    I am just not sure how to do this within SQL. And yes, before any asks, it has to be done on the server and not within the client application.


    I am using MS SQL 2005. Thanks.

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Look into PIVOT, or some type of loop to append full name for each fk_IntakeMainID.
    Last edited by PMASchmed; 10-29-08 at 13:14.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You could try using CTE's; here's a reasonable start for you to play with
    Code:
    DECLARE @personnel table (
       id   int
     , main int
     , name varchar(200)
    )
    
    INSERT INTO @personnel (id, main, name)
          SELECT 1, 1, 'Bob Smith'
    UNION SELECT 2, 1, 'Dow Jones'
    UNION SELECT 3, 2, 'Roberta Smith'
    UNION SELECT 4, 1, 'John Dow'
    UNION SELECT 5, 3, 'Perry Kerr'
    UNION SELECT 6, 3, 'Jen Chow'
    
    ; WITH cte AS (
      SELECT id
           , main
           , Convert(varchar(Max), name) As [name]
      FROM   @personnel x
      WHERE  id IN (SELECT Min(id) FROM @personnel GROUP BY main)
        UNION ALL
          SELECT p.id
               , p.main
               , Convert(varchar(Max), c.name + ', ' + p.name)
          FROM   cte c
           INNER
            JOIN @personnel p
              ON c.main = p.main
             AND c.id < p.id
    )
    SELECT *
    FROM   cte
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    There's gotta be a better way of doing this, this CTE looks hideous!
    Code:
    DECLARE @personnel table (
       id   int
     , main int
     , name varchar(200)
    )
    
    INSERT INTO @personnel (id, main, name)
          SELECT 1, 1, 'Bob Smith'
    UNION SELECT 2, 1, 'Dow Jones'
    UNION SELECT 3, 2, 'Roberta Smith'
    UNION SELECT 4, 1, 'John Dow'
    UNION SELECT 5, 3, 'Perry Kerr'
    UNION SELECT 6, 3, 'Jen Chow'
    
    ; WITH cte AS (
      SELECT id
           , main
           , Convert(varchar(Max), name) As [name]
           , 1 As [n]
      FROM   @personnel x
      WHERE  id IN (SELECT Min(id) FROM @personnel GROUP BY main)
        UNION ALL
          SELECT p.id
               , p.main
               , Convert(varchar(Max), c.name + ', ' + p.name)
               , n + 1
          FROM   cte c
           INNER
            JOIN @personnel p
              ON c.main = p.main
             AND c.id < p.id
    )
    SELECT c.main
         , c.name
    FROM   cte c
     INNER
      JOIN (
            SELECT main
                 , Max(n) As [max_n]
            FROM   cte
            GROUP
                BY main
           ) As [top_ones]
        ON c.main = top_ones.main
       AND c.n = top_ones.max_n
    ORDER
        BY main
    ...but it does work
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    There's gotta be a better way of doing this...
    there is, it's called GROUP_CONCAT
    Code:
    SELECT fk_IntakeMainID
         , GROUP_CONCAT(FullName) AS FullNames
      FROM tblPersonnel 
    GROUP
        BY fk_IntakeMainID
    it works on strings the same way SUM works on numbers -- instead of adding together a column of numbers, it adds together (concatenates, with an optional separator, with optional sorting) strings

    sadly, it's only available in the world's flagship database system, mysql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So, the best MySQL features don't even belong in databases anyway? I mean, this is really a presentation issue...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so the correct query would be...
    Code:
    SELECT fk_IntakeMainID
         , FullName
      FROM tblPersonnel 
    ORDER
        BY fk_IntakeMainID
    ?? ?? ??????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2008
    Posts
    4
    Thanks One and all for your suggestions. It is too bad I am not up to speed enough to understand everything. Please forgive the idoit questions that follow:

    What is throwing me off is this part of the George's code:

    Code:
    INSERT INTO @personnel (id, main, name)
          SELECT 1, 1, 'Bob Smith'
    UNION SELECT 2, 1, 'Dow Jones'
    UNION SELECT 3, 2, 'Roberta Smith'
    UNION SELECT 4, 1, 'John Dow'
    UNION SELECT 5, 3, 'Perry Kerr'
    UNION SELECT 6, 3, 'Jen Chow'
    Would I have to write a "UNION SELECT" statement for each of the 1000+ records? Meaning also that I would have to update this code everytime new records were added.

    THis is killing me. It should be easy, or so I thought.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh sorry, that was just me setting up some test data.
    @personnel is a table variable that I used as a mock up of your set up.

    The query itself kicks in from the semi-colon onwards
    George
    Home | Blog

  10. #10
    Join Date
    Oct 2008
    Posts
    4
    You freak'in rule! See the attached screen shot. My final question is how do I now tell the query to pull the data from my tblPersonnal table?

    Again, I am sorry for the stupid questions.
    Attached Thumbnails Attached Thumbnails Concentrat_ss.JPG  

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's not stupid at all, you've just not seen this type of construct before.

    Just to reiterate what I said earlier, in the code I posted, @personnel is a table variable that I constructed in my environment so that I could test the logic.

    The bit that actually does the clever stuff is this
    Code:
    ; WITH cte AS (
      SELECT id
           , main
           , Convert(varchar(Max), name) As [name]
           , 1 As [n]
      FROM   @personnel x
      WHERE  id IN (SELECT Min(id) FROM @personnel GROUP BY main)
        UNION ALL
          SELECT p.id
               , p.main
               , Convert(varchar(Max), c.name + ', ' + p.name)
               , n + 1
          FROM   cte c
           INNER
            JOIN @personnel p
              ON c.main = p.main
             AND c.id < p.id
    )
    SELECT c.main
         , c.name
    FROM   cte c
     INNER
      JOIN (
            SELECT main
                 , Max(n) As [max_n]
            FROM   cte
            GROUP
                BY main
           ) As [top_ones]
        ON c.main = top_ones.main
       AND c.n = top_ones.max_n
    ORDER
        BY main
    So what you need to do is replace the fields and table names with those that exist in your personnel table, and hopefully it will all work lovely.

    give it a go and post back your efforts
    George
    Home | Blog

  12. #12
    Join Date
    Oct 2008
    Posts
    4
    It worked wonderfully. Thank you. I can't tell you how much time you just saved me.

Posting Permissions

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