Combining Rows Based on Common Column SQL Server

Question

I am very new to SQL and all self taught so please keep that in mind when getting too technical!

I am working with a Church database. We have a table that assigns a family ID to sets of people who are in a family. I have a table that contains all the family IDs and has a column with the unique person IDs that are associated with that family ID. When I run a query against the table looking for a specific family ID, I get a row for each unique person ID that is associated with that family ID.

What I need to do is combine the families into one row displaying the adult's first names in one column and the children's first names in another. Then the last name in a column and the address in a column.

I can do this successfully when I use this:

DECLARE @FirstNames VARCHAR (MAX)
DECLARE @Children VARCHAR (MAX)
DECLARE @Address VARCHAR (MAX)
DECLARE @LastName VARCHAR (MAX)

SELECT @FirstNames = COALESCE(@FirstNames+', ','') + pb.first_name,
@Address = pb.Address,
@LastName = pb.last_name
FROM core_v_person_basic pb
JOIN core_family_member fm ON fm.person_id = pb.person_id
WHERE fm.role_luid = 29
AND fm.family_id = 13783

SELECT @Children = COALESCE(@Children+', ','') + pb.first_name
FROM core_v_person_basic pb
JOIN core_family_member fm ON fm.person_id = pb.person_id
WHERE fm.role_luid = 31
AND fm.family_id = 13783

SELECT
@FirstNames AS 'First Names',
@LastName AS 'Last Name',
@Children AS 'Children',
@Address AS 'Address'

But the issue is this only works for one family ID. I want to be able to either display all the family IDs formatted this way, or display a certain set of family IDs. For example, I may want to display all the families that are in a certain class which will come from a different table.

I need a solution that is flexible enough for me to change it around to add different columns to the results based on need.

Thanks so much for any help!

UPDATE Here's a query that I modified from Dimitar Kyuchukov's answer: -- Simulate your tables for sample's sake (you would replace these variables with your table names in the code till the end of the sample):

DECLARE @core_family_member AS TABLE 
           (family_id int,
           person_id int,
           date_created datetime,
           date_modified datetime,
           created_by varchar(50),
           modified_by varchar(50),
           role_luid int,
           organization_id int)

DECLARE @core_v_person_basic AS TABLE (
           person_id int,
           guid uniqueidentifier,
           suffix varchar(50),
           nick_name nvarchar(50),
           first_name nvarchar(50),
           last_name nvarchar(50),
           birth_date datetime,
           address_id int,
           Address varchar(201),
           street_address_1 varchar(100),
           street_address_2 varchar(100),
           city varchar(64),
           state varchar(12),
           postal_code varchar(24),
           member_status_luid int,
           member_status varchar(50),
           record_status varchar(8),
           gender varchar(1),
           marital_status_luid int,
           marital_status varchar(50),
           home_phone varchar(50),
           list_home_phone varchar(50),
           business_phone varchar(50),
           list_business_phone varchar(50),
           cell_phone varchar(50),
           list_cell_phone varchar(50),
           email varchar(80),
           area_id int,
           area_name varchar(100),
           organization_id int,
           photo_guid varchar(80),
           envelope_number int,
           restricted bit)
INSERT INTO 
    @core_v_person_basic

SELECT * FROM core_v_person_basic

INSERT INTO 
    @core_family_member

SELECT * FROM core_family_member

-- Here children and parents are selected separately with a family ID to join by (these are called CTE-s for Common Table Expression - they will make the final statement more clear):
;WITH 
Children AS (
    SELECT 
        fm.family_id,
        pb.first_name AS child_first_name
    FROM 
        @core_v_person_basic pb
        INNER JOIN @core_family_member fm 
            ON fm.person_id = pb.person_id
    WHERE
        fm.role_luid = 31
),
Parents AS (
    SELECT 
        fm.family_id,
        pb.first_name AS parent_first_name
    FROM 
        @core_v_person_basic pb
        INNER JOIN @core_family_member fm 
            ON fm.person_id = pb.person_id
    WHERE
        fm.role_luid = 29
),
Address AS (
    SELECT
        pb.address AS 'Address'
    FROM
        @core_v_person_basic pb)

-- Here stuff() function is used to concatenate the members grouped by family ID - basically an XML is "stuffed" similar to your variables and then the ready value is taken using the value() function:
SELECT
    stuff((
            SELECT 
                ', ' + p.parent_first_name 
            FROM
                Parents p
            WHERE 
                f.family_id = p.family_id
            FOR XML PATH(''), TYPE
        ).value('(./text())[1]', 'VARCHAR(MAX)')
        , 1, 1, '') AS parents_names,
    stuff((
            SELECT 
                ', ' + c.child_first_name 
            FROM
                Children c
            WHERE 
                f.family_id = c.family_id
            FOR XML PATH(''), TYPE
        ).value('(./text())[1]', 'VARCHAR(MAX)')
        , 1, 1, '') AS children_names

FROM
    Parents f
GROUP BY
    f.family_id

This is much closer to what I need, but I still can't add any more columns than what is already there. It seems to be because of the Group By syntax. Any help with how to bring up additional columns would be awesome!

SOLUTION! I got it working just as I need! Here's the working query with even more columns and information!

WITH 
Children AS (
    SELECT 
        fm.family_id,
        pb.first_name AS child_first_name
    FROM 
        core_v_person_basic pb
        INNER JOIN core_family_member fm 
            ON fm.person_id = pb.person_id
    WHERE
        fm.role_luid = 31
),
Parents AS (
    SELECT 
        fm.family_id,
        pb.Address,
        pb.city,
        pb.state,
        pb.postal_code,
        pb.record_status,
        pb.last_name,
        pb.first_name AS parent_first_name
    FROM 
        core_v_person_basic pb
        INNER JOIN core_family_member fm 
            ON fm.person_id = pb.person_id
    WHERE
        fm.role_luid = 29
),
RecordStatus AS (
    SELECT
        fm.family_id,
        pb.record_status AS record_status
    FROM
        core_v_person_basic pb
        JOIN core_family_member fm On fm.person_id = pb.person_id
),
InactiveReason AS (
    SELECT
        fm.family_id,
        ir.inactive_reason_value AS inactive_reason
    FROM
        core_person cp
        JOIN core_family_member fm ON cp.person_id = fm.person_id
        JOIN core_v_inactive_reason ir ON ir.inactive_reason_id = cp.inactive_reason_luid
)

-- Here stuff() function is used to concatenate the members grouped by family ID - basically an XML is "stuffed" similar to your variables and then the ready value is taken using the value() function:
SELECT
    stuff((
            SELECT 
                ', ' + p.parent_first_name
            FROM
                Parents p
            WHERE 
                f.family_id = p.family_id
            FOR XML PATH(''), TYPE
        ).value('(./text())[1]', 'VARCHAR(MAX)')
        , 1, 1, '') AS 'First Name(s)',
f.last_name,
    stuff((
            SELECT 
                ', ' + c.child_first_name 
            FROM
                Children c
            WHERE 
                f.family_id = c.family_id
            FOR XML PATH(''), TYPE
        ).value('(./text())[1]', 'VARCHAR(MAX)')
        , 1, 1, '') AS 'Children',

f.Address,
f.city,
f.state,
f.postal_code,
 STUFF((
            SELECT
                ', ' + r.record_status
            FROM
                RecordStatus r
            WHERE
                f.family_id = r.family_id
            FOR XML PATH(''), TYPE
        ).value('(./text())[1]', 'VARCHAR(MAX)')
        , 1, 1, '') AS 'Record Status',
 STUFF((
            SELECT
                ', ' + i.inactive_reason
            FROM
                InactiveReason i
            WHERE
                f.family_id = i.family_id
            FOR XML PATH(''), TYPE
        ).value('(./text())[1]', 'VARCHAR(MAX)')
        , 1, 1, '') AS 'Record Status'
FROM
    Parents f
GROUP BY
    f.family_id,
    f.Address,
    f.city,
    f.state,
    f.postal_code,
    f.last_name

Thanks everyone for the help!

here's one example wich rolls up multiple rows into one row based on the family ID's. If you were to do this for parent and children columns, you should have what you're after http://stackoverflow.com/questions/15477743/listagg-in-sqlserver — xQbert, Nov 12 '15 at 17:24
You could start with a select of distinct family_ids, and then use sub-selects to get adult first names and children. — Tab Alleman, Nov 12 '15 at 18:34
xQbert, that works pretty well, but not if I need to bring in another table. This allows me to see all the person IDs associated with the family ID, but I need to then pull in a table that tells me the first and last name of the persons who have that person ID. — Nick Taylor, Nov 12 '15 at 18:36

Dimitar Kyuchukov · Accepted Answer · 2015-11-13T17:45:07.673

You can try the stuff() function to concatenate results grouped by family_id - here is a sample, I think it returns what you need to.

I am afraid I can't make it much simpler - unfortunately SQL server does not have a group_concat function and stuff() is really ugly to read or write for concatenation purposes. You can add more columns to the Children and Parents CTE-s, but if a concatenation is required you will need to add separate stuff() calls for each new concatenated column.

-- Simulate your tables for sample's sake (you would replace these variables with your table names in the code till the end of the sample), this is just demo:
DECLARE @core_v_person_basic AS TABLE (
    person_id INT,
    first_name VARCHAR(30)
)

DECLARE @core_family_member AS TABLE (
    person_id INT,
    role_luid INT,
    family_id INT
)

INSERT INTO 
    @core_v_person_basic
VALUES
    --- family 1
    (1, 'Alexander'),
    (2, 'Diana'),
    (3, 'Nick'),
    (4, 'Betty'),
    --- family 2
    (5, 'Gustav'),
    (6, 'Lory'),
    (7, 'Peter'),
    (8, 'Ally'),
    --- family 3 (no children here)
    (9, 'Chuck'),
    (10, 'Sarah')

INSERT INTO 
    @core_family_member
VALUES
    --- family 1
    (1, 29, 1),
    (2, 29, 1),
    (3, 31, 1),
    (4, 31, 1),
    --- family 2
    (5, 29, 2),
    (6, 29, 2),
    (7, 31, 2),
    (8, 31, 2),
    --- family 3
    (9, 29, 3),
    (10, 29, 3)

-- End of demo data preparation, below comes the actual sample.

-- Here children and parents are selected separately with a family ID to join by (these are called CTE-s for Common Table Expression - they will make the final statement more clear):
;WITH 
Children AS (
    SELECT 
        fm.family_id,
        pb.first_name AS child_first_name
    FROM 
        -- you should use your table names here, not the demo variables:
        @core_v_person_basic pb
        INNER JOIN @core_family_member fm 
            ON fm.person_id = pb.person_id
    WHERE
        fm.role_luid = 31
),
Parents AS (
    SELECT 
        fm.family_id,
        pb.first_name AS parent_first_name
    FROM 
        -- you should use your table names here, not the demo variables:
        @core_v_person_basic pb
        INNER JOIN @core_family_member fm 
            ON fm.person_id = pb.person_id
    WHERE
        fm.role_luid = 29
)

-- Here stuff() function is used to concatenate the members grouped by family ID - basically an XML is "stuffed" similar to your variables and then the ready value is taken using the value() function:
SELECT
    stuff((
            SELECT 
                ',' + p.parent_first_name 
            FROM
                Parents p
            WHERE 
                f.family_id = p.family_id
            FOR XML PATH(''), TYPE
        ).value('(./text())[1]', 'VARCHAR(MAX)')
        , 1, 1, '') AS parents_names,
    stuff((
            SELECT 
                ',' + c.child_first_name 
            FROM
                Children c
            WHERE 
                f.family_id = c.family_id
            FOR XML PATH(''), TYPE
        ).value('(./text())[1]', 'VARCHAR(MAX)')
        , 1, 1, '') AS children_names
FROM
    Parents f
GROUP BY
    family_id

This looks like a great solution. Also, thanks for the detailed explanation of what each section does!But I have a question. In the INSERT INTO, how would I get all the information for a table in there without having to type out all the info from the columns? And there are close to 40 columns that I might need to see at once. — Nick Taylor, Nov 13 '15 at 16:48
I was able to get the right columns in, but did it in a very clunky way. I did a Script Table as and INSERT to. If you know of a better way, I'd love to hear it! See my update on my first post. — Nick Taylor, Nov 13 '15 at 17:37
@NickTaylor Sorry for the confusion - you don't need to use the table variables at all (that was just to make a working example) - just start from the CTE-s (i.e. ";WITH ...") and replace the table variables with your table names (i.e. core_v_person_basic and core_family_member). — Dimitar Kyuchukov, Nov 13 '15 at 17:39
@NickTaylor, I have just added a few more comments to show which part is for demo purpose and which part you should concentrate on (see the commends within the JOIN-s and the "-- End of demo data preparation"). — Dimitar Kyuchukov, Nov 13 '15 at 17:48
Thank you! That is helpful. I am still unable to add any additional columns, however. Here's what I added to the end of your sample to try and display the Address column from core_v_person_basic: (SELECT p.address FROM Parents p ) AS Address When I do this I get this error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. — Nick Taylor, Nov 13 '15 at 18:05
I don't need to concatenate the addresses as there is normally only 1 per family. — Nick Taylor, Nov 13 '15 at 18:08
I figured it out! I just added the extra columns I needed to the query under one of the CTEs and then called it at the end using the alias for that CTE. Thank you so much for your help! I will update my original post with the solution. — Nick Taylor, Nov 13 '15 at 19:03
As you say address is probably the same for the entire family in the scope you work on - so adding your address column to the Parents CTE and then max(f.address) to the final query select section would work. The max() is actually dummy aggregation so that the SQL server does not scream for the group-by (i.e. as it is the same for the entire family, max(f.address) will be same as f.address, but max() will allow the grouping in the final query). — Dimitar Kyuchukov, Nov 13 '15 at 21:58

Combining Rows Based on Common Column SQL Server

1 Answers1