Concentrating Many Records into One Field

smbarney · #1 (**permalink**) 10-29-08, 11:22

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.

PMASchmed · #2 (**permalink**) 10-29-08, 12:11

Look into PIVOT, or some type of loop to append full name for each fk_IntakeMainID.

gvee · #3 (**permalink**) 10-29-08, 12:35

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

gvee · #4 (**permalink**) 10-29-08, 12:59

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

r937 · #5 (**permalink**) 10-29-08, 14:46

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

blindman · #6 (**permalink**) 10-29-08, 15:28

So, the best MySQL features don't even belong in databases anyway? I mean, this is really a presentation issue...

r937 · #7 (**permalink**) 10-29-08, 15:51

so the correct query would be...

Code:

SELECT fk_IntakeMainID
     , FullName
  FROM tblPersonnel 
ORDER
    BY fk_IntakeMainID

?? ?? ??????

smbarney · #8 (**permalink**) 10-29-08, 17:28

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.

gvee · #9 (**permalink**) 10-29-08, 19:15

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

smbarney · #10 (**permalink**) 10-30-08, 09:14

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.

gvee · #11 (**permalink**) 10-30-08, 10:08

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

smbarney · #12 (**permalink**) 10-30-08, 11:18

It worked wonderfully. Thank you. I can't tell you how much time you just saved me.

Internet Services

Web Development

Digital Marketing

Consumer Tech