SQL Pivot / Case Query based on Row Value-CodePudding

Problem

Using SQL Server, I'm trying to pivot data based on values in a column. I want to move Bob and John's value column over if Salary is in the metric column.

Sample data:

Person table

Person    ID
-------------
Bob       1
Bob       1
John      2
John      2

Value table

Metric   Value    ID
---------------------
Age      52       1
Salary   60000    1
Age      45       2
Salary   55000    2

Expected output

My goal is to pivot the table if salary is present in the Metric column.

Person    Metric   Value   Salary   ID
---------------------------------------
Bob       Age      52      60000    1
John      Age      45      55000    2

Current code:

SELECT *
FROM person_table pt, value_table vb
WHERE pt.id = vb.id
  AND vb.metric IN ('Age', 'Salary')

CodePudding user response：

Use the following pivot query:

SELECT
    pt.Person,
    'Age' AS Metric,
    MAX(CASE WHEN vb.Metric = 'Age'    THEN vb.Value END) AS Value,
    MAX(CASE WHEN vb.Metric = 'Salary' THEN vb.Value END) AS Salary,
    pt.ID
FROM person_table pt
INNER JOIN value_table vb
    ON pt.id = vb.id
GROUP BY
    pt.Person,
    pt.ID
ORDER BY
    pt.ID;