Using CTE for comparing data in multiple rows

Question

In this dataset I need to mark out application ID P-10900 because it is touched by 2 TeamMember. We can mark it out in form of a flag created as separate column.

I know it can be done using recursive CTE easily but not sure how to write the syntax.

TeamMember      Status      ApplicationID
NULL            NW          P-10900
John            IR          P-10900
John            CO          P-10900
Dick            IR          P-10900
Dick            PA          P-10900
NULL            NW          P-06700
John            IR          P-06700
John            PA          P-06700
NULL            NW          P-14600
Andre           IR          P-14600

This is an extension to the question asked here

Find which rows have different values for a given column in SQL

I am using MS SQL Server 2012

I am expecting output like this:

TeamMember        Status        ApplicationID   Flag
NULL              NW            P-10900         Dup
John              IR            P-10900         Dup
John              CO            P-10900         Dup
Dick              IR            P-10900         Dup
Dick              PA            P-10900         Dup
NULL              NW            P-06700         Alike   
John              IR            P-06700         Alike
John              PA            P-06700         Alike
NULL              NW            P-14600         Alike
Andre             IR            P-14600         Alike

Here is the Schema:

CREATE TABLE App
    ([TeamMember] varchar(5), [aStatus] varchar(2), [ApplicationID] varchar(7))
;

INSERT INTO App
    ([TeamMember], [aStatus], [ApplicationID])
VALUES
    (NULL, 'NW', 'P-10900'),
    ('John', 'IR', 'P-10900'),
    ('John', 'CO', 'P-10900'),
    ('Dick', 'IR', 'P-10900'),
    ('Dick', 'PA', 'P-10900'),
    (NULL, 'NW', 'P-06700'),
    ('John', 'IR', 'P-06700'),
    ('John', 'PA', 'P-06700'),
    (NULL, 'NW', 'P-14600'),
    ('Andre', 'IR', 'P-14600')
;

This is what I attempted which ofcourse is not working:

 WITH cte (applicationid, status, teammember)
     AS (SELECT applicationid,
                status,
                teammember
         FROM   app
         GROUP  BY applicationid,
                   status,
                   teammember)
SELECT applicationid,
       teammember,
       status,
       CASE
         WHEN (SELECT DISTINCT CASE
                                 WHEN Count(applicationid) > 1 THEN 'Alike'
                                 ELSE 'Dup'
                               END
               FROM   cte B
               WHERE  B.teammember = A.teammember
               GROUP  BY B.teammember) = 'Alike' THEN 'Alike'
         ELSE 'Dup'
       END AS Flag
FROM   cte A
ORDER  BY applicationid

Be more explicit - what output are you trying to get (**exactly**)? — Amit, Mar 08 '16 at 22:18

score 1 · Accepted Answer · answered Mar 09 '16 at 06:37

You're simply overcomplicating things. You don't need a CTE at all here:

SELECT a.*, dups.Flag
FROM app a INNER JOIN (
    SELECT ApplicationID, CASE
          WHEN MIN(TeamMember) = MAX(TeamMember) THEN 'Alike'
          ELSE 'Dup'
        END Flag
    FROM app t
    GROUP BY ApplicationID) dups
  ON a.ApplicationID = dups.ApplicationID

Using CTE for comparing data in multiple rows

1 Answers1