CODEWITHSUNDEEP
sql-server
Michal
Michal

Reputation: 5857

SQL Server - select and join parent-child records, pivot and group

The real problem is probably less complex than the subject might suggest.

The schema

Three tables (simplified). There can be multiple people within one membership. The main member is identified as 1 in person_membership.relationship, there can be a spouse of the main member identified as '2' and other dependants (any other number).

  CREATE TABLE person
      ([first_name] varchar(10), [surname] varchar(10), [date_of_birth] date, [person_id] int);

  INSERT INTO person
      ([first_name], [surname], [date_of_birth] ,[person_id])
  VALUES
      ('Alice', 'AA', '1/1/1990', 1),
      ('Bob'  , 'AA', '1/1/1990', 2),
      ('Carol', 'AA', '1/1/1990', 3),
      ('Dave' , 'AA', '1/1/1990', 4),
      ('Elen' , 'AA', '1/1/1990', 5),
      ('Fran' , 'BB', '1/1/1990', 6),
      ('Gaby' , 'BB', '1/1/1990', 7)

  CREATE TABLE person_membership
      ([person_id] int, [status_flag] varchar(1), [membership_id] int, [relationship] int);

  INSERT INTO person_membership
      ([person_id], [status_flag], [membership_id], [relationship])
  VALUES
      (1, 'A', 10, 1),
      (2, 'A', 10, 2),
      (3, 'A', 10, 3),
      (4, 'A', 10, 4),
      (5, 'A', 10, 4),
      (6, 'A', 20, 1),
      (7, 'A', 20, 3);

  CREATE TABLE memship
      ([membership_id] int, [memship_status] varchar(1));

  INSERT INTO memship
      ([membership_id], [memship_status])
  VALUES
      (10, 'A'),
      (20, 'A');

The task

Simplified version - display membership and person id of the main member and identify if they have a spouse, and also if they have any dependants show how many of them they are.

The desired output

The basic version something like the screenshot below. Main member ID, their person ID, Yes/No field for spouse, number of dependants: enter image description here

The current solution

The code below works and creates the desired output but it seems very clunky. There must be more elegant ways of doing it?

    WITH peeps AS (SELECT person.person_id,
                          person.first_name,
                          person.surname,
                          person_membership.membership_id,
                          person_membership.relationship
                   FROM  memship, person, person_membership
                   WHERE person_membership.person_id = person.person_id 
                     AND memship.membership_id = person_membership.membership_id
                     AND person_membership.status_flag='A'
                     AND memship.memship_status='A')

    SELECT final.MemID, final.PersonID, sum(final.Spouse) as 'Spouse?', sum(final.Kids) as 'No of kids'
    FROM (
    SELECT
        peeps1.membership_id                 AS 'MemID',
        CASE peeps1.relationship
            WHEN 1 THEN peeps1.person_id
                    ELSE        peeps2.person_id
        END                                  AS 'PersonID',
        SUM (CASE WHEN
            peeps1.relationship = 2 THEN 1
            ELSE 0
            END )                                 AS 'Spouse',   
        SUM (CASE WHEN
            peeps1.relationship > 2 THEN 1
            ELSE 0
            END )                                 AS 'Kids'                             
    FROM peeps peeps1 
    INNER JOIN peeps peeps2 ON peeps2.membership_id = peeps1.membership_id AND peeps2.relationship = 1
    GROUP BY peeps1.membership_id, peeps1.relationship, peeps1.person_id, peeps2.person_id) as final
    GROUP BY final.MemID, final.PersonID

Upvotes: 2

Views: 324

Answers (3)

user12342125
user12342125

Reputation: 

SELECT M.membership_id,
       MAX(CASE WHEN PM.relationship = 1 THEN P.person_id ELSE NULL END) person_id,
       COUNT(CASE WHEN PM.relationship = 2 THEN 1 ELSE 0 END )AS 'Spouse',   
       COUNT(CASE WHEN PM.relationship IN (3, 4, 5) THEN 1 ELSE 0 END ) AS 'Kids'        
FROM  memship M 
JOIN person_membership PM ON  M.membership_id = PM.membership_id
JOIN person P ON PM.person_id = P.person_id 
WHERE PM.status_flag='A' AND M.memship_status='A' 
GROUP BY M.membership_id

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try This

FIDDLE DEMO

SELECT M.membership_id,
       MAX(CASE WHEN PM.relationship = 1 THEN P.person_id ELSE NULL END) person_id,
       SUM (CASE WHEN PM.relationship = 2 THEN 1 ELSE 0 END )AS 'Spouse',   
       SUM (CASE WHEN PM.relationship > 2 THEN 1 ELSE 0 END ) AS 'Kids'        
FROM  memship M 
JOIN person_membership PM ON  M.membership_id = PM.membership_id
JOIN person P ON PM.person_id = P.person_id 
WHERE PM.status_flag='A' AND M.memship_status='A' 
GROUP BY M.membership_id

Upvotes: 2

Squirrel
Squirrel

Reputation: 24763

Simplified query

SELECT  MemID        = pm.membership_id,
        PersonID     = MAX (CASE WHEN pm.relationship = 1 THEN p.person_id END),
        [Spouce?]    = SUM (CASE WHEN pm.relationship = 2 THEN 1 ELSE 0 END),
        [No of kids] = SUM (CASE WHEN pm.relationship NOT IN (1, 2) THEN 1 ELSE 0 END)
FROM    person p
        INNER JOIN person_membership pm ON  p.person_id         = pm.person_id
        INNER JOIN memship m            ON  pm.membership_id    = m.membership_id
WHERE   pm.status_flag      = 'A'
AND     m.memship_status    = 'A' 
GROUP BY pm.membership_id

Note : please avoid using old style of join. User INNER JOIN instead

Upvotes: 1

Related Questions