SQL Server Append a column to a TSQL Pivot or CASE Pivot?

8i9zcol2 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(106)

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.

sql-server

来源：https://stackoverflow.com/questions/76758177/append-a-column-to-a-tsql-pivot-or-case-pivot

2条答案

按热度按时间

dwbf0jvd1#

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

赞(0）回复(0）举报 2023-08-02

5rgfhyps2#

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

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Append a column to a TSQL Pivot or CASE Pivot?

2条答案

相关问题

热门标签

最新问答