January 19, 2018 at 2:39 pm
I can't seem to figure out how to write this in SQL. I have searched and read about Pivots but I think that I"m already joining a lot of other tables it might be throwing me off how to do.
I basically have a query that I am collecting all sorts of data on and now I’m to the point in my query when I need to create columns for the actual field values with amounts that pertains to that field value.
Example:
| Student ID | AwardYear | Complete? | CompleteDate | FundName | Amount |
| 12345 | 2017-2018 | Y | 1/10/2018 | SubLoan | 1750 |
| 12345 | 2017-2018 | Y | 1/10/2018 | Unsub | 3500 |
| 12345 | 2017-2018 | Y | 1/10/2018 | Pell | 5000 |
| 45678 | 2017-2018 | Y | 1/2/2018 | SubLoan | 1750 |
| 45678 | 2017-2018 | Y | 1/2/2018 | Pell | 5000 |
What I would like it to output
| Student ID | AwardYear | Complete? | CompleteDate | SubLoan | UnSub | Pell |
| 12345 | 2017-2018 | Y | 1/10/2018 | 1750 | 3500 | 5000 |
| 45678 | 2017-2018 | Y | 1/2/2018 | 1750 | 0 | 5000 |
January 19, 2018 at 2:55 pm
Since you're not providing actual table layouts, this will be an approximation. That said - the query would look something like:
select [Student ID], AwardYear,[Complete?],CompleteDate,Subloan,Unsub,Pell
from MyTableName
pivot (
sum(amount) for FundName in ([Subloan],[Unsub],[Pell])
) pvt
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 19, 2018 at 3:03 pm
The thing is that the fund names will not always be these three there could be more.
January 19, 2018 at 3:13 pm
Here is my actual code currently.
SELECT distinct iv.studentid, iv.externalId3
, case when iv.primaryEFCType = '1' then 'Dependent'
when iv.primaryEFCType = '2' then 'Independent without Dependents'
when iv.primaryEFCType = '3' then 'Independent with Dependents'
when iv.primaryEFCType = '4' then 'Dependents'
when iv.primaryEFCType = '5' then 'Independent without Dependents'
when iv.primaryEFCType = '6' then 'Independent with Dependents'
else NULL end
, iv.federalAwardYearName
, '1' as FAFSACompletedYN
, iv.dateApplicationCompleted as FAFSACompletedDate
, case iv.studentIsSelectedForVerification when 'Y' then '1' else '0' end as SelectedForVerification
, case iv.studentIsSelectedForVerification when 'Y' then (select distinct max(dr1.reviewedDate)
from dataExtract_DocumentRequirement_View dr1
where dr1.studentId = iv.studentid
and dr1.scopeValue = iv.federalAwardYearName
and dr1.status in ('SATISFIED','WAIVED')
and dr1.activityLogEntryTypeCode = 'VerificationRelated'
and not exists (select 'x' from dataExtract_DocumentRequirement_View b
where b.studentId = dr1.studentId
and b.status = 'NEEDED'
and b.activityLogEntryTypeCode = 'VerificationRelated')) else null end as VerifiedCompleteDate
, case when (select sum(av1.acceptedAmount) from dataExtract_Award_View_v002 av1, dataExtract_LoanPeriod_View_v001 lv1
where av1.studentId = av.studentId
and av1.loanPeriodId = lv1.loanPeriodId
and lv1.federalAwardYear = iv.federalAwardYearName) > 0 then '1' else '0' end as AcceptedYN
, '' as AcceptedDate
, case dr2.status when 'SATISFIED' then '1' else '0' end as ELCCompletedYN
, dr2.reviewedDate as ELCCompletedDate
, case dr3.status when 'SATISFIED' then '1' else '0' end as MPNCompletedYN
, dr3.reviewedDate as MPNCompletedDate
, case when (select sum(av2.paidAmount) from dataExtract_Award_View_v002 av2, dataExtract_LoanPeriod_View_v001 lv2
where av2.studentId = av.studentId
and av2.loanPeriodId = lv2.loanPeriodId
and lv2.federalAwardYear = iv.federalAwardYearName) > 0 then '1' else '0' end as DisbursedYN
, fv.fund_name
, av.offeredAmount
from dataExtract_ISIR_View iv
JOIN dataExtract_Award_View_v002 AS av
ON (iv.studentid = av.studentid)
JOIN dataExtract_LoanPeriod_View_v001 AS lv
ON (av.studentid = lv.studentid
and av.loanPeriodId = lv.loanPeriodId
and lv.federalAwardYear = iv.federalAwardYearName)
INNER JOIN dataExtract_DocumentRequirement_View AS dr2
ON (iv.studentid = dr2.studentid)
INNER JOIN dataExtract_DocumentRequirement_View AS dr3
ON (iv.studentid = dr3.studentid)
INNER JOIN dataExtract_AwardDisbursement_View_v001 adv
ON (iv.studentid = adv.studentid)
JOIN dataExtract_Fund_Config_View fv
ON (av.fundid = fv.fundid)
where federalAwardYearName = '2017-2018'
and dr2.documentExternalId = 'FAELC'
and dr3.documentExternalId in ('FAMPN','FAMPNPLUS')
and adv.awardId = av.awardId
;
January 19, 2018 at 9:40 pm
If the query needs to have dynamic headers you probably want to acquaint yourself with Jeff Moden's work on cross tabs and pivots.
CrossTabs and Pivots, Part 2
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply