SQL Pivot / Case Query based on Row Value

Question

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')

@DannySlor Microsoft SQL Server - sorry will update the question — KL_
– KL_, Commented Oct 20, 2022 at 15:04

Tim Biegeleisen · Accepted Answer · 2022-10-20 15:05:35Z

2

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;

answered Oct 20, 2022 at 15:05

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Pivot / Case Query based on Row Value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related