问当两个值匹配时，SQL server比较多列并显示文本。

EN

SELECT *,
    [area injured] =
        STUFF((
            CASE WHEN head >= face AND head >= thor AND head >= abdo AND head >= spine THEN ', head' ELSE '' END +
            CASE WHEN face >= head AND face >= thor AND face >= abdo AND face >= spine THEN ', face' ELSE '' END +
            CASE WHEN thor >= head AND thor >= face AND thor >= abdo AND thor >= spine THEN ', thor' ELSE '' END +
            CASE WHEN abdo >= head AND abdo >= face AND abdo >= thor AND abdo >= spine THEN ', abdo' ELSE '' END +
            CASE WHEN spine >= head AND spine >= face AND spine >= abdo AND spine >= thor THEN ', spine' ELSE '' END
        ), 1, 2, '')
FROM tbl

WITH CteUnpivot AS(
    SELECT
        t.ID, x.col, x.value
    FROM tbl t
    CROSS APPLY(VALUES
        ('head', t.head),
        ('face', t.face),
        ('thor', t.thor),
        ('abdo', t.abdo),
        ('spine', t.spine)
    )x(col, value)
),
CteRn AS(
    SELECT *,
        rn = RANK() OVER(PARTITION BY ID ORDER BY value DESC)
    FROM CteUnpivot
)
SELECT 
    c.ID,
    [area injured] =
        STUFF((
            SELECT ', ' + col
            FROM CteRn
            WHERE
                ID = c.ID
                AND rn = 1
            FOR XML PATH('')
        ),1 ,2, '')
FROM CteRn c
GROUP BY c.ID

WITH cte AS
(
  SELECT ID
    ,area_injured = REPLACE((SELECT 
                              IIF(t1.head = cr.val, 'head, ', '') +
                              IIF(t1.face = cr.val, 'face, ', '') +
                              IIF(t1.thor = cr.val, 'thor, ', '') +
                              IIF(t1.abdo = cr.val, 'abdo, ', '') + 
                              IIF(t1.spine = cr.val, 'spine, ', '') + ';'
                            FROM tab t1
                            WHERE t.ID = t1.ID), ', ;', '')
  FROM tab t
  CROSS APPLY(SELECT MAX(v)
              FROM (VALUES (t.head),
                           (t.face),
                           (t.thor),
                           (t.abdo),
                           (t.spine)) AS c(v)) AS cr(val)
)
UPDATE t
SET area_injured = c.area_injured
FROM tab t
JOIN cte c
  ON t.id = c.id;

SELECT *
FROM tab;

WITH CTE2 AS
(
   SELECT * FROM
    (SELECT ID,HEAD,FACE,THOR,ABDO,SPINE
     FROM PARTSDATA) T
        UNPIVOT
        ( QUANTITY FOR PART IN
        (HEAD,FACE,THOR ,ABDO , SPINE)
        ) AS U
),
CTE3 AS
(
    SELECT RANK() OVER(PARTITION BY ID ORDER BY QUANTITY DESC) AS RN,QUANTITY,PART,ID
    FROM CTE2
)

SELECT DISTINCT C1.ID,STUFF(DT.CONTENT,1,1,'') AS [AREA INJURED] 
FROM CTE3 C1
        CROSS APPLY(SELECT ','+PART 
                    FROM CTE3
                    WHERE ID = C1.ID AND RN=1 FOR XML PATH(''))DT(CONTENT)
WHERE RN=1