Raw data:

ID  Age Date        Value
-------------------------
1   5   01/01/2023  10
1   5   01/04/2023  15
2   7   01/02/2023  17
3   9   10/02/2022  7
3   9   12/20/2022  9

Desired output:

1. One ID/Age per row (Age will always be the same per ID)
2. Use latest Date partitioned by ID if has multiple dates
3. Pivot the Value column into two separate columns, Value_1 and Value_2
4. If an ID does not have 2nd value, in the output, leave Value_2 blank
5. Value_1 is the highest, Value_2 is second highest

This is what the output should look like:

ID  Age Date        Value_1   Value_2
-------------------------------------
1   5   01/04/2023    15        10
2   7   01/02/2023    17    
3   9   12/20/2022     9         7

I couldn't figure it out even after reading the PIVOT reference. It is little bit different than the example I read over. The column I am pivoting is a numeric column, not categorical. I need some help here. https://www.techonthenet.com/sql_server/pivot.php

My attempt/some ideas:

select *
from 
    (select 
         ID, Age, Date,
         Value,
         row_number() over (partition by ID order by Date desc) row_num
     from 
         table) a
where 
    a.row_num = 1


SELECT 
    ID, Age, Date, 'Value_1', 'Value_2'
FROM
    (SELECT 
         ID, Age, Date, Value
     FROM 
         table) AS SourceTable
PIVOT
    (SUM(Value)
     FOR Date IN ('day1', 'day2')
    ) AS PivotTable;

Updates: both @dale-k and @t-n's solution are good. If you have more than 2 values you can try @t-n's PIVOT approach. Thanks for your help!

CodePudding user response：

The following query returns your desired results using standard aggregation with a case expression.

select
    ID
    , Age
    , max([Date]) [Date]
    , max([Value]) Value_1
    , case when min([Value]) <> max([Value]) then min([Value]) else null end Value_2
from @MyTable
group by ID, Age;

Returns:

CodePudding user response：

For a solution that uses ROW_NUMBER() and PIVOT and can be expanded to more than two Value columns, try:

SELECT PVT.ID, PVT.Age, PVT.Date,
    [1] AS Value_1, [2] AS Value_2
FROM (
    SELECT
        ID, Age, Value,
        MAX(Date) OVER(PARTITION BY ID, Age) AS Date,
        ROW_NUMBER() OVER(PARTITION BY ID, Age ORDER BY Value DESC) AS RN
    FROM @Data D
) Source
PIVOT (
    MAX(Source.Value)
    FOR Source.RN IN ([1], [2])
) PVT
ORDER BY PVT.ID, PVT.Age

See this db<>fiddle.