Denormalize data in a query or view
From my phone, so apologies for misspellings.
I need to denormalize data from the following table:
tblReferralTasks
TaskID - pk
ReferralID - long
TaskDesc - nvarchar(50)
DueDate
Date Completed
CompletedBy
TaskDesc contains a number of values , but the 3 I’m interested in are
‘3 Month Follow-up’
‘6 Month Follow-up’
‘12 Month Follow-up’
What i need is on recoed for each referral with:
ReferralID, Due3Mo, Comp3Mo , Due6Mo, Comp6Mo , Due12Mo, Comp12Mo
Where those columns are the date columns
Last Comment
ASKER
Not sure that is what I'm looking for Andrei. Let me clarify:
Thanks, Dale
TaskID ReferralID TaskDesc DueDate DateCompleted CompletedBy
3000 1791 Closeout Report 2020-12-15 2020-12-14 John
3001 1791 3 Month Follow-up 2021-03-01 2021-03-06 John
3002 1791 6 Month Follow-up 2021-06-01
3003 1791 12 Month Follow-up 2021-12-01
ReferralID Due3Mo Comp3Mo Due6Mo Comp6Mo Due12Mo Comp12Mo
1791 2021-03-01 2021-03-03 2021-06-01 NULL 2021-12-01 NULL
SELECT ReferralID
, MAX(SQ.Due3Mo) as Due3Mo
, MAX(SQ.Comp3Mo) as Comp3Mo
, MAX(SQ.Due6Mo) as Due6Mo
, MAX(SQ.Comp6Mo) as Comp6Mo
, MAX(SQ.Due12Mo) as Due12Mo
, MAX(SQ.Comp12Mo) as Comp12Mo
FROM (
SELECT ReferralID
, CASE WHEN TaskDesc = '3 Month Follow-up' THEN DueDate ELSE NULL END as Due3Mo
, CASE WHEN TaskDesc = '3 Month Follow-up' THEN DateCompleted ELSE NULL END as Comp3Mo
, CASE WHEN TaskDesc = '6 Month Follow-up' THEN DueDate ELSE NULL END as Due6Mo
, CASE WHEN TaskDesc = '6 Month Follow-up' THEN DateCompleted ELSE NULL END as Comp6Mo
, CASE WHEN TaskDesc = '12 Month Follow-up' THEN DueDate ELSE NULL END as Due12Mo
, CASE WHEN TaskDesc = '12 Month Follow-up' THEN DateCompleted ELSE NULL END as Comp12Mo
FROM tblReferralTasks
) as SQ
GROUP BY SQ.ReferralID
Thanks, Dale
another version:
SELECT ReferralID
, MAX(CASE WHEN opt = 'DueDate' THEN [3 Month Follow-up] ELSE NULL END) as Due3Mo
, MAX(CASE WHEN opt = 'DateCompleted' THEN [3 Month Follow-up] ELSE NULL END) as Comp3Mo
, MAX(CASE WHEN opt = 'DueDate' THEN [6 Month Follow-up] ELSE NULL END) as Due6Mo
, MAX(CASE WHEN opt = 'DateCompleted' THEN [6 Month Follow-up] ELSE NULL END) as Comp6Mo
, MAX(CASE WHEN opt = 'DueDate' THEN [12 Month Follow-up] ELSE NULL END) as Due12Mo
, MAX(CASE WHEN opt = 'DateCompleted' THEN [12 Month Follow-up] ELSE NULL END) as Comp12Mo
FROM tblReferralTasks as SQ
unpivot
(
v for opt in
(
[DueDate], [DateCompleted]
)
) up
pivot
(
max(v)
for TaskDesc in
(
[3 Month Follow-up], [6 Month Follow-up], [12 Month Follow-up]
)
) p
Group By ReferralID
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Ryan,
What would be the advantages or disadvantages of using the technique you suggest over the technique that Scott suggested, which is really just a refinement of my query.
Scott,
What is the advantage of moving the Case statements within the Max() functions rather than creating a subquery as in my example?
What would be the advantages or disadvantages of using the technique you suggest over the technique that Scott suggested, which is really just a refinement of my query.
Scott,
What is the advantage of moving the Case statements within the Max() functions rather than creating a subquery as in my example?
More efficient, that's mostly it.
ASKER
using unpivot and pivot is more efficient?
Do you know what version of SQL Server did these come into use? I'm still running 2008R2 on this particular clients server, not by choice, mind you.
Dale
Do you know what version of SQL Server did these come into use? I'm still running 2008R2 on this particular clients server, not by choice, mind you.
Dale
I never use unpivot / pivot unless absolutely forced to.
I was talking about the efficiency of doing the MAX() in the first part of the query, rather than potentially passing the entire table to generate 7 columns and then doing MAX() on all those extracted rows.
I was talking about the efficiency of doing the MAX() in the first part of the query, rather than potentially passing the entire table to generate 7 columns and then doing MAX() on all those extracted rows.
ASKER
I assume we could gain efficiency by adding a WHERE clause:
SELECT ReferralID
, MAX(CASE WHEN TaskDesc = '3 Month Follow-up' THEN DueDate ELSE NULL END) as Due3Mo
, MAX(CASE WHEN TaskDesc = '3 Month Follow-up' THEN DateCompleted ELSE NULL END) as Comp3Mo
, MAX(CASE WHEN TaskDesc = '6 Month Follow-up' THEN DueDate ELSE NULL END) as Due6Mo
, MAX(CASE WHEN TaskDesc = '6 Month Follow-up' THEN DateCompleted ELSE NULL END) as Comp6Mo
, MAX(CASE WHEN TaskDesc = '12 Month Follow-up' THEN DueDate ELSE NULL END) as Due12Mo
, MAX(CASE WHEN TaskDesc = '12 Month Follow-up' THEN DateCompleted ELSE NULL END) as Comp12Mo
FROM tblReferralTasks
WHERE [TaskDesc] IN ('3 Month Follow-up', '6 Month Follow-up', '12 Month Follow-up')
GROUP BY ReferralID
ORDER BY ReferralID
Yes, you're absolutely correct about that. A WHERE clause is a good idea.
ASKER
Question cleanup.
Thanks, Scott. I've been using this technique for a while now, where I know the values which I want as column headers and it seems to work well.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
SELECT ReferralID, DueDate, DateAdd(month,3,DueDate) AS Due3Mo,
DateAdd(month,6,DueDate) AS Due6Mo, DateAdd(month,12,DueDate) AS Due12Mo,
DateCompleted, DateAdd(month,3,DateCompleted) AS Comp3Mo,
DateAdd(month,6,DateCompleted) AS Comp6Mo,
DateAdd(month,12,DateCompleted) AS Comp12Mo
FROM ...