I've got some data in a table that looks like the following. I'm trying to run a query that will get my data on a single row per requestId. I don't need the dates or the denial reason just te eprojman and apvStatus for each groupId
requestId - projMan1 - apvStatus1 - projMan2 - apvStatus2 - projMan3 - apvStatus3 etc.. for all 5 groupIds
requestId | groupId | entryDate | approvalDate | apvStatus | projMan | denialReason |
---|---|---|---|---|---|---|
1 | 1 | 2020-11-02 | 2019-07-25 | APPROVED | rx1942 | NULL |
1 | 2 | 2020-11-02 | 2019-07-25 | APPROVED | ma2674 | NULL |
1 | 3 | 2020-11-02 | 2019-07-25 | APPROVED | cb9097 | NULL |
1 | 4 | 2020-11-02 | 2019-07-25 | APPROVED | bj1763 | NULL |
1 | 5 | 2020-11-02 | 2019-07-25 | APPROVED | tr5972 | NULL |
2 | 1 | 2020-11-02 | NULL | NOT APPROVED | NULL | 6 |
2 | 2 | 2020-11-02 | NULL | PENDING | ma2674 | NULL |
2 | 3 | 2020-11-02 | NULL | PENDING | cb9097 | NULL |
2 | 4 | 2020-11-02 | NULL | PENDING | bj1763 | NULL |
2 | 5 | 2020-11-02 | NULL | PENDING | tr5972 | NULL |
I've been trying to use a PIVOT table but all the examples I find involves summing data or something. I just pretty much want to take the 5 rows and turn it into 1 for each requestID
The only thing I've been able to come up with is to select from the same table 5 times for each groupID and union it but that's slower than heck. Got to be a better way
Thanks.
Current query:
select group1.requestId
, group1.apvStatus as apvStatus1
, group1.projMan as projMan1
, group2.apvStatus as apvStatus2
, group2.projMan as projMan2
, group3.apvStatus as apvStatus3
, group3.projMan as projMan3
,group4.apvStatus as apvStatus4
, group4.projMan as projMan4
,group5.apvStatus as apvStatus5
, group5.projMan as projMan5
,group1.denialReason
INTO #TEMPBAOrganized
from (
select requestId, apvStatus, projMan, denialReason from #TEMPBULKAPPROVAL where groupId = 1) group1
INNER JOIN
(select requestId, apvStatus, projMan, denialReason from #TEMPBULKAPPROVAL where groupId = 2) group2
on group1.requestId = group2.requestId
INNER JOIN
(select requestId, apvStatus, projMan from #TEMPBULKAPPROVAL where groupId = 3) group3
on group1.requestId = group3.requestId
INNER JOIN
(select requestId, apvStatus, projMan from #TEMPBULKAPPROVAL where groupId = 4) group4
on group1.requestId = group4.requestId
INNER JOIN
(select requestId, apvStatus, projMan from #TEMPBULKAPPROVAL where groupId = 5) group5
on group1.requestId = group5.requestId
CodePudding user response:
You can do it with something like this:
SELECT
requestId,
approvalDate_1 = MAX(approvalDate_1),
approvalDate_2 = MAX(approvalDate_2),
approvalDate_3 = MAX(approvalDate_3),
approvalDate_4 = MAX(approvalDate_4),
approvalDate_5 = MAX(approvalDate_5),
projMan_1 = MAX(projMan_1),
projMan_2 = MAX(projMan_2),
projMan_3 = MAX(projMan_3),
projMan_4 = MAX(projMan_4),
projMan_5 = MAX(projMan_5)
FROM
(
SELECT
requestId,
groupId,
approvalDate,
projMan,
'approvalDate_' CAST( (DENSE_RANK() OVER(PARTITION BY requestId ORDER BY groupId)) AS VARCHAR(2)) AS approvalDatePivot,
'projMan_' CAST( (DENSE_RANK() OVER(PARTITION BY requestId ORDER BY groupId)) AS VARCHAR(2)) AS projManPivot
FROM
@tbl
) T
PIVOT (
MAX(approvalDate) FOR approvalDatePivot IN ([approvalDate_1],[approvalDate_2],[approvalDate_3],[approvalDate_4],[approvalDate_5])
) pvt_1
PIVOT (
MAX(projMan) FOR projManPivot IN ([projMan_1],[projMan_2],[projMan_3],[projMan_4],[projMan_5])
) pvt_2
GROUP BY requestId
CodePudding user response:
For pivoting of multiple column, it is easier to use CASE
expression with aggregate.
select t.requestId,
projMan1 = max(case when t.groupId = 1 then t.projMan end),
apvStatus1 = max(case when t.groupId = 1 then t.apvStatus end),
projMan2 = max(case when t.groupId = 2 then t.projMan end),
apvStatus2 = max(case when t.groupId = 2 then t.apvStatus end),
projMan3 = max(case when t.groupId = 3 then t.projMan end),
apvStatus3 = max(case when t.groupId = 3 then t.apvStatus end),
projMan4 = max(case when t.groupId = 4 then t.projMan end),
apvStatus4 = max(case when t.groupId = 4 then t.apvStatus end),
projMan5 = max(case when t.groupId = 5 then t.projMan end),
apvStatus5 = max(case when t.groupId = 5 then t.apvStatus end)
from #TEMPBULKAPPROVAL t
group by t.requestId
Note : max
is also an aggregate function