CODEWITHSUNDEEP
sql-server
GullitsMullet
GullitsMullet

Reputation: 358

SQL server compare multiple columns and display text when two values match

I have a table with multiple columns identifying body area injured:

ID      head    face    thor   abdo    spine   area injured
4721    5       0       5      3       0       multiple 
4723    0       0       0      3       3       multiple
4737    0       0       1      3       0       abdo
4752    0       2       0      0       0       face

At present, the [area injured] column is based on the column that contains the highest value, if two columns have the joint highest value, it lists 'multiple'.

I want to change the [area injured] column and list the actual body areas instead of 'multiple', for example:

ID      head    face    thor   abdo    spine   area injured
4721    5       0       5      3       0       head, thor   
4723    0       0       0      3       3       abdo, spine
4737    0       0       1      3       0       abdo
4752    0       2       0      0       0       face

How do I compare the five columns to establish which columns have the highest values?

Upvotes: 1

Views: 13463

Answers (3)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

You case use UNPIVOT to achieve this

SQL Fiddle

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

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176014

To UPDATE values in area_injured column you can use:

SqlFiddleDemo

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;
  1. CROSS APPLY will get MAX value from columns with body part
  2. Correlated subquery build area_injured string
  3. Update original table based on Id

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

One way is to use CASE expressions find the columns with the largest value:

SQL Fiddle

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

Another way, which is more dynamic, is to UNPIVOT your data and use RANK to get the column with the highest value. Then, use FOR XML PATH('') for concatenation:

SQL Fiddle

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

Upvotes: 2

Related Questions