Using SQL, how do I select which column to add a value to, based on the contents of the row?

Question

I'm having a difficult time phrasing the question, so I think the best thing to do is to give some example tables. I have a table, Attribute_history, I'm trying to pull data from that looks like this:

ID   Attribute_Name  Attribute_Val  Time Stamp
---  --------------  -------------  ----------
1    Color           Red            2022/09/28 01:00
2    Color           Blue           2022/09/28 01:30
1    Length          3              2022/09/28 01:00
2    Length          4              2022/09/28 01:30
1    Diameter        5              2022/09/28 01:00
2    Diameter        10             2022/09/28 01:30
2    Diameter        11             2022/09/28 01:32

I want to create a table that pulls the attributes of each ID, and if the same ID and attribute_name has been updated, pull the latest info based on Time Stamp.

ID    Color   Length  Diameter
----  ------  ------- -------- 
1     Red     3       5 
2     Blue    4       11

I've achieved this by nesting several select statements, adding one column at a time. I achieved selecting the latest date using this stack overflow post. However, this code seems inefficient, since I'm selecting from the same table multiple times. It also only chooses the latest value for an attribute I know is likely to have been updated multiple times, not all the values I'm interested in.

SELECT
    COLOR, DIAMETER, DATE_
FROM
(
    SELECT
        COLORS.COLOR, ATTR.ATTRIBUTE_NAME AS DIAMETER, ATTR.TIME_STAMP AS DATE_, RANK() OVER (PARTITION BY COLORS.COLOR ORDER BY ATTR.TIME_STAMP DESC) DATE_RANK -- https://stackoverflow.com/questions/3491329/group-by-with-maxdate
    FROM
    (
        SELECT
            ATTRIBUTE_HISTORY.ATTRIBUTE_VAL
        FROM
            ATTRIBUTE_HISTORY
        WHERE
            ATTRIBUTE_HISTORY.ATTRIBUTE_NAME = 'Color'
        GROUP BY ATTRIBUTE_HISTORY.ID
    ) COLORS
    INNER JOIN ATTRIBUTE_HISTORY ATTR ON COLORS.ID = ATTR.ID
    WHERE
        ATTR.ATTRIBUTE_NAME = 'DIAMETER' 
)
WHERE
    DATE_RANK = 1

(I copied my real query and renamed values with Find+Replace to obscure the data so this code might not be perfect, but it gets across the idea of how I'm achieving my goal now.)

How can I rewrite this query to be more concise, and pull the latest date entry for each attribute?

Why do you GROUP BY in the derived table? (I see no aggregate function.) — jarlh, Sep 28 '22 at 17:44
Which dbms are you using? Is it oracle like in the link you provided? — DannySlor, Sep 28 '22 at 17:45
@jarlh it's to remove duplicates. The real table has duplicates for some things. — Tom Kaufman, Sep 28 '22 at 17:59

score 1 · Accepted Answer · answered Sep 29 '22 at 12:56

For MS SQL Server

Your Problem has 2 parts:

Identify the latest Attribute value based on Time Stamp Column
Convert the Attribute Names to columns ( Pivoting ) in the final result.

Solution:

;with CTEx as
(
    select 
    row_number() over(partition by id, Attr_name order by Time_Stamp desc) rnum,
    id,Attr_name, Attr_value, time_stamp
    from #temp
)
SELECT * FROM   
(
    SELECT id,Attr_name,Attr_value
    FROM CTEx 
    where rnum = 1
) t 
PIVOT(
    max(Attr_value) 
    FOR Attr_name IN (Color,Diameter,[Length])
) AS pivot_table;

First part of the problem is taken care of by the CTE with the help of ROW_NUMBER() function. Second part is achieved by using PIVOT() function.

Definition of #temp for reference

Create table #temp(id int, Attr_name varchar(200), Attr_value varchar(200), Time_Stamp datetime)

Using SQL, how do I select which column to add a value to, based on the contents of the row?

1 Answers1