SQL Server : multiple rows single line

gfttwv5a 于 2023-03-17 发布在 SQL Server

关注(0)|答案(2)|浏览(179)

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.

sql-server

来源：https://stackoverflow.com/questions/68717246/sql-server-multiple-rows-single-line

2条答案

按热度按时间

i7uq4tfw1#

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

赞(0）回复(0）举报 2023-03-17

9wbgstp72#

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.

赞(0）回复(0）举报 2023-03-17

我来回答

SQL Server : multiple rows single line

2条答案

相关问题

热门标签

最新问答

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

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

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