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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy