SQL Server How DO I write a SQL query to show related records in one row

8cdiaqws 于 5个月前发布在其他

关注(0)|答案(1)|浏览(77)

I need an SQL Server query that will list the select rows from the first table and list the related records from the second table on the same row as the first table record.

Here is the dataset from the first table

tblName
 NID    Name
 1      John Doe
 2      Jane Doe

Here is the dataset from the second table

tblTask
TID    Task                     EstMin    NID
1      Fix the bed              45        1
2      Clean room               30        1
3      Wipe the floor           20        1
4      Cook breakfast           45        2
5      Clean the dishes         30        2

The NID columns on both tables relate to them. I need a query that will generate the dataset below

Result dataset
NID    Name       Task            EstMin  Task               EstMin     Task             EstMin
1      John Doe   Fix the bed     45      Clean room         30         Wipe the floor   20
2      Jane Doe   Cook breakfast  45      Clean the dishes   30

sql-server

来源：https://stackoverflow.com/questions/77624843/how-do-i-write-a-sql-query-to-show-related-records-in-one-row

1条答案

按热度按时间

jdgnovmf1#

You can use conditional aggregation as follows:

SELECT T.NID, T.NAME,
       MAX(CASE WHEN RN = 1 THEN S.TASK END) AS TASK1,
       MAX(CASE WHEN RN = 1 THEN S.ESTMIN END) AS ESTMIN1,
       MAX(CASE WHEN RN = 2 THEN S.TASK END) AS TASK2,
       MAX(CASE WHEN RN = 2 THEN S.ESTMIN END) AS ESTMIN2,
       MAX(CASE WHEN RN = 3 THEN S.TASK END) AS TASK3,
       MAX(CASE WHEN RN = 3 THEN S.ESTMIN END) AS ESTMIN3
FROM
   (SELECT F.NID, F.NAME,
           ROW_NUMBER() OVER (PARTITION BY F.NID ORDER BY S.TID) AS RN  
     FROM FIRST_TABLE F
     JOIN SECOND_TABLE S ON F.NID = S.NID) T
GROUP BY T.NID, T.NAME;

赞(0）回复(0）举报 5个月前

我来回答

SQL Server How DO I write a SQL query to show related records in one row

1条答案

相关问题

热门标签

最新问答