Making right joins and using STUFF

Question

I have 2 tables.

Tabel T1:

-----ID-----Name----
     1       P1
     2       P2
     3       P3

Tabel T2:

-----ID-----PID----Type
     1       1      T1 
     1       2      T2
     1       3      T1
     2       4      T3
     2       5      T3

What I want:

  ----Name----Different_Types-----Type_Names
       P1           2               T1,T2

What I have tried:

Select distinct T1.NAME,
                count(distinct T2.Type) as Different_Types,
                from T1
                left join T2
                on T2.ID = T1.ID
                having count(distinct T2.PID) > 2
                order by Different_Types desc

Whit this query I have the two first columns in my desired table, but having trouble adding the third....Any idea's ?

`STUFF` takes a string and makes a change within that string (removing some chars, inserting other). What you're almost certainly thinking of is the (ab)use of the `FOR XML` functionality in SQL Server to perform string concatenation. `STUFF` is then wrapped around that use to *remove a leading comma from the result*. Thinking that `STUFF` is the thing responsible for string concatenation is to entirely put the cart before the horse. — Damien_The_Unbeliever, Oct 24 '18 at 14:28
Possible duplicate of [How to make a query with group\_concat in sql server](https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) — S3S, Oct 24 '18 at 14:36
Depending on what version of SQL Server you have, you can use either built-in functions to do your concatenation, or use `FOR XML` to "hack" your concatenation. — Zack, Oct 24 '18 at 14:36
What version of SQL Server are you using? (Please always specify, using a tag.) — Aaron Bertrand, Oct 24 '18 at 14:40

score 3 · Accepted Answer · answered Oct 24 '18 at 14:36

Think this should do what you are after

DECLARE @T1 TABLE
(
    ID INT NOT NULL,
    Name CHAR(2) NOT NULL
);

INSERT INTO @T1
VALUES (1,'P1'),(2,'P2'),(3,'P3');

DECLARE @T2 TABLE
(
    ID INT NOT NULL,
    PID INT NOT NULL,
    TypeName CHAR(2) NOT NULL
);

INSERT INTO @T2
VALUES (1,1,'T1'),(1,2,'T2'),(1,3,'T1'),(2,4,'T3'),(2,5,'T3');

SELECT  T1.Name,
        COUNT(DISTINCT T2.TypeName) AS Different_Types,
        STUFF((
                    SELECT  ',' + T.TypeName
                    FROM    @T2 AS T
                    WHERE   T1.ID = T.ID
                    GROUP   BY T.TypeName
                    FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
                ,1,1,'')
FROM    @T1 AS T1
INNER
JOIN    @T2 AS T2
        ON  T1.ID = T2.ID
GROUP   BY T1.ID, T1.Name
HAVING  COUNT(DISTINCT T2.PID) > 2;

Edit:

Changed your LEFT to an INNER join as you are referencing the T2 table in the having clause anyway.

That did it :) Thank you very much ! – Joe Doe Oct 24 '18 at 14:55 — Joe Doe, Oct 24 '18 at 14:55

Making right joins and using STUFF

1 Answers1