SQL Server : multiple rows single line

Question

I would like to get the representation of one record based on the primary key value from multiple tables. As shown below, each table can have multiple values based on this primary key value.

TABLE-1

ID	NAME
1	AA
2	BB
3	CC
4	DD
5	EE

TABLE-2

ID	SCHOOL	AUT
1	11	A
2	11	A
2	12	B
3	11	A
4	12	A
4	13	B
5	13	A

TABLE-3

ID	TC
1	101
2	102
2	103
2	104
3	105
4	106
4	107
5	108

The result below is the value obtained with an OUTER JOIN.

SELECT
    T1.ID, T2.SCHOOL, T3.TC, T2.AUT 
FROM  
    T1
LEFT OUTER JOIN 
    T2 ON T1.ID = T2.ID
LEFT OUTER JOIN
    T3 ON T1.ID = T3.ID
ORDER BY  
    T1.ID ASC

ID	SCHOOL	TC	AUT
1	11	101	A
2	11	102	A
2	12	102	B
2	11	103	A
2	12	103	B
2	11	104	A
2	12	104	B
3	11	105	A
4	12	106	A
4	13	106	B
4	12	107	A
4	13	107	B
5	13	106	A

How can I get the result like below?

ID	SCHOOL	TC1	TC2	TC3
1	11	101
2	11	102	103	104
3	11	105
4	12	106	107
5	13	108

The important thing here is that in the result value, SCHOOL only shows that AUT is 'A'.

I would appreciate it if you let me know your query.

Answer 1

SELECT 
    T1.ID, T2.SCHOOL, 
    GROUP_CONCAT(T3.TC), 
    GROUP_CONCAT(T2.AUT) 
FROM 
    T1 
LEFT OUTER JOIN 
    T2 ON T1.ID = T2.ID 
LEFT OUTER JOIN 
    T3 ON T1.ID = T3.ID 
GROUP BY 
    T1.ID, T2.SCHOOL 
WHERE 
    T2.AUT = ‘A’ 
ORDER BY 
    T1.ID ASC

Notice that GROUP_CONCAT concatenates the values in the row.

EDIT: oh my, haven't seen that it's a SQL Server question!

Just replace GROUP_CONCAT with STRING_AGG if you’re using SQL Server 2017 or newer.