Append a column to a TSQL Pivot or CASE Pivot?

Question

Given the following example data:

ID	Subject	Value	Date
AAA	Field	Cosi	July 23
BBB	Amount	99	July 22
AAA	Field	Drui	July 24
AAA	Amount	87	July 23

I am attempting to write a TSQL Pivot or CASE Pivot about my Subject column. Ultimately, my query should be:

Grouping by ID.
Pivoting (or 'spreading') on Subject.
Aggregating on Value.
And including the respective date in its own column.

Further, when grouping by ID, I wish to only return the row with the most current date for a given subject and ID. Though I imagine this can be done in a second query.

The transformed data should look like this:

ID	FieldValue	FieldDate	AmountValue	AmountDate
AAA	Drui	July 24	87	July 23
BBB	Null	Null	99	July 22

An answer that is only able to do one "subject" at a time would also work:

First Query

ID	FieldValue	FieldDate
AAA	Drui	July 24
BBB	Null	Null

Second Query

ID	AmountValue	AmountDate
AAA	87	July 23
BBB	99	July 22

So far, I have created some really dumb Pivots and CASE pivots that ultimately did not work. So I have since started over, thinking that I am likely overcomplicating this query. Right now, I have two very simple CASE Pivots:

A CASE Pivot that does a single pivot on the "Field" subject

SELECT 
  [ID],
  MAX(CASE WHEN [Subject] = 'Field' THEN [Value] ELSE '' END) AS FieldValue
FROM Table1
GROUP BY [ID]

And a CASE Pivot that correctly returns the latest date for a given ID

SELECT 
  [ID],
  MAX(CASE WHEN [Subject] = 'Field' THEN [Date] ELSE '' END) AS ChangedDate
FROM Table1
GROUP BY [ID]

But I can't figure out how to - if this makes sense - combine those two steps.
I've considered that maybe I need to take the second query that correctly returns the latest date and merely JOIN the Value column, but the way I'm about to go about it seems incorrect.

What RDBMS are you using? T-SQL is used by several products. — Thom A, Jul 24 '23 at 21:43
Sounds like you need to pivot your rows with another often asked query of "top n rows per group", have you searched for that? — Stu, Jul 24 '23 at 21:46
@Stu I think that was exactly what I was looking for! I found this prior thread: https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group and slightly modified Josh Gilfillan's answer to PARTITION BY multiple columns. So far it seems to have worked. I'm going to double check it indeed worked and then I'll edit and mark my question as solved. Thanks man! — spike817, Jul 24 '23 at 22:17
Don't mark your question as solved, add your own answer and accept it — Dale K, Jul 24 '23 at 22:20

score 0 · Accepted Answer · answered Jul 24 '23 at 22:28

This should give you what you need.

I've changed the names of some columns so they don't conflict with keywords and I change the dates to actual dates but the principle is still the same if you only have part of a date as long as you can sort it correctly.

I've recreated your data, then created simple CTE to return only the latest rows. Finally I just FULL JOINED two simple queries to get the desired result.

DROP TABLE IF EXISTS #t;
CREATE TABLE #t(ID varchar(10), Subjct varchar(10), Value varchar(10), dt date);
INSERT INTO #t VALUES 
('AAA', 'Field', 'Cosi', '2023-07-23'),
('BBB', 'Amount', '99', '2023-07-22'),
('AAA', 'Field', 'Drui', '2023-07-24'),
('AAA', 'Amount', '87', '2023-07-23');

with cte as 
( SELECT * 
    FROM (SELECT *, RowN = ROW_NUMBER() OVER(PARTITION BY ID, Subjct ORDER BY dt DESC) FROM #t) x
WHERE RowN = 1) 

SELECT fld.ID, fld.FieldValue, fld.FieldDate, amt.AmountValue, amt.AmountDate FROM 
    (
    SELECT 
        ID
        , FieldValue = Subjct
        , FieldDate = dt
        FROM cte
        WHERE Subjct = 'Field'
     ) fld 
FULL JOIN 
    (SELECT 
      ID
    , AmountValue = Subjct
    , AmountDate = dt
    FROM cte
    WHERE Subjct = 'Amount'
    ) amt
    ON fld.ID = amt.ID ;

Here's the final output

score 0 · Answer 2 · answered Jul 24 '23 at 23:00

User Stu sent me down the right rabbit hole. I was looking to query "top n rows per group."

See Josh Gilfillan's answer: Get top 1 row of each group

I modified Josh's answer slightly as I needed to PARTITION BY multiple columns. In my case I needed to partition by both [ID] and [Subject] to correctly return the most recent entry for any given combination of those two columns.

SELECT TOP 1 WITH TIES
 [ID]
 ,[Subject]
 ,[Values]
 ,[Date]
FROM table1
ORDER BY row_number() over (partition by [ID], [Subject] order by [Date] desc)

Which produces:

ID	Subject	Values	Date
AAA	Amount	87	2023-07-14
AAA	Field	Drui	2023-07-24
BBB	Amount	99	2023-07-22

sqlfiddle

This only answers the second part of your question. I've added a full answer (which includes a variation on this to get your latest data only) — Alan Schofield, Jul 24 '23 at 23:17

Append a column to a TSQL Pivot or CASE Pivot?

2 Answers2