CODEWITHSUNDEEP
sqlsql-server
JSK
JSK

Reputation: 5

SQL Server : multiple rows single line

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.

Upvotes: 0

Views: 1027

Answers (2)

Lior Pollak
Lior Pollak

Reputation: 3450

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.

Upvotes: 0

Stu
Stu

Reputation: 32609

It looks, from your desired results, you just need to use row_number in combination with a conditional aggregate. Your sample data seems a little inadequate, I can't see any requirement for table1 at all.

Try the following:

with t as (
    select t2.id,t2.school,t3.tc, Row_Number() over(partition by t2.id order by t3.tc) col
    from t2 join t3 on t2.id=t3.id
    where aut='A'
)
select id,school,
    max(case when col=1 then tc end) TC1,
    max(case when col=2 then tc end) TC2,
    max(case when col=3 then tc end) TC3
from t
group by id, school

Example SQL Fiddle

Upvotes: 2

Related Questions