将多个行值合并到单个列中

Gav_Ed 发表于 Dev

Gav_Ed

我感谢以前曾提出过类似的问题，但我不确定下一步该怎么做，并且承受着一定的压力。我正在尝试将多个行值合并到一个列中，并且这样做是在尝试使用XML Path。以下是我必须去的地方，但现在我仅在一个列中显示多个主题的多个实例。

我想使用SQL Server将给定瞳孔（大约10个）的所有科目合并到“所有科目”列中。有人可以指出我要去哪里了吗？谢谢加文

SELECT distinct
P.FORM AS Class, 
NAME.NAME AS [Pupil Name], 
(SELECT ';' + SS.DESCRIPTION
FROM PUPIL P 
INNER JOIN PUPIL_SET PS on PS.PUPIL_ID = P.PUPIL_ID
INNER JOIN SUBJECT_SET SS on SS.SUBJECT_SET_ID = PS.SUBJECT_SET_ID 
FOR XML PATH('')) [All Subjects],
NAME_1.TITLE + ' ' + NAME_1.FIRST_NAMES + ' ' + NAME_1.SURNAME AS [Parent or     Carer Name],
Replace(isnull(ADDRESS.HOUSE_STREET,'') + ', ' +     isnull(ADDRESS.VILLAGE_AREA,'') + ', ' + isnull(ADDRESS.TOWN_CITY,'') + ', ' +     isnull(ADDRESS.COUNTY,'') + ', ' + isnull(ADDRESS.COUNTRY,'') + ' ' +     isnull(ADDRESS.POST_CODE,''),',,', '') AS Address,
--RELATIONSHIP.RANK,
CASE WHEN NAME.MAIN_ADDRESS_ID = NAME_1.MAIN_ADDRESS_ID THEN 'HOME' ELSE    'OTHER' END AS [Home or Other]
FROM PUPIL P
INNER JOIN NAME ON P.NAME_ID = NAME.NAME_ID
INNER JOIN ADDRESS ON NAME.MAIN_ADDRESS_ID = ADDRESS.ADDRESS_ID
INNER JOIN RELATIONSHIP ON NAME.NAME_ID = RELATIONSHIP.FROM_NAME_ID
INNER JOIN NAME AS NAME_1 ON RELATIONSHIP.TO_NAME_ID = NAME_1.NAME_ID
INNER JOIN PUPIL_SET PS on PS.PUPIL_ID = P.PUPIL_ID
INNER JOIN SUBJECT_SET SS on SS.SUBJECT_SET_ID = PS.SUBJECT_SET_ID
WHERE 
(RELATIONSHIP.RANK=1 Or RELATIONSHIP.RANK=2) 
AND P.ACADEMIC_YEAR=YEAR(DateAdd(m,-5,getDate()))
AND P.SUB_SCHOOL='030SEN' 
AND P.IN_USE='y' 
AND P.RECORD_TYPE='1' 
AND Len(P.FORM)>0
and p.ACADEMIC_YEAR = 2015;

戈登·利诺夫（Gordon Linoff）

首先，你只需要subject_set和pupil_set子查询，而不是外部查询。

其次，您需要一个关联子句。因此，如下所示：

SELECT P.FORM AS Class,
       . . .
       (SELECT ';' + SS.DESCRIPTION
        FROM PUPIL_SET PS INNER JOIN
             SUBJECT_SET SS
             on SS.SUBJECT_SET_ID = PS.SUBJECT_SET_ID 
        WHERE PS.PUPIL_ID = P.PUPIL_ID
        FOR XML PATH('')
       ) [All Subjects],
       . . .
FROM PUPIL P INNER JOIN
     NAME
     ON P.NAME_ID = NAME.NAME_ID INNER JOIN
     ADDRESS
     ON NAME.MAIN_ADDRESS_ID = ADDRESS.ADDRESS_ID INNER JOIN
     RELATIONSHIP
     ON NAME.NAME_ID = RELATIONSHIP.FROM_NAME_ID INNER JOIN
     NAME AS NAME_1
     ON RELATIONSHIP.TO_NAME_ID = NAME_1.NAME_ID
WHERE  . . .;

您不需要SELECT DISTINCT。由于不必要的联接，您获得了重复项。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。