Combine rows into pivoted output

Question

Post reply

Combine rows into pivoted output

Chrissy321

SSCoach

Points: 15768

January 26, 2024 at 10:13 pm

#4368372

Hello and thanks if you can help,

I am trying to combine 2 rows into pivoted out. One of the pivoted column needs to come form one row and the second pivoted column needs to come form the other row.

I am working in a view and this is a much highly simplified example so looking for idea to accomplish this. My data table is quite large so I probably can't stage multiple CTEs.

DROP TABLE IF EXISTS #Test
SELECT * INTO  #Test FROM
(
SELECT 1 TestID,'1 Month'  AS  ReportingPeriod,'Type1' AS CalculationType, 1 AS Calculation UNION ALL
SELECT 1 TestID,'1 Month'  AS  ReportingPeriod,'Type2' AS CalculationType, 3 AS Calculation UNION ALL
SELECT 1 TestID,'2 Month'  AS  ReportingPeriod,'Type1' AS CalculationType, 4 AS Calculation UNION ALL
SELECT 1 TestID,'2 Month'  AS  ReportingPeriod,'Type2' AS CalculationType, 5 AS Calculation
) T

SELECT * FROM #Test

--Here is my current pivot
SELECT
*
FROM
(
SELECT
*
FROM #Test
) T
PIVOT(MAX(Calculation) FOR ReportingPeriod IN ([1 Month],[2 Month])) AS P

--The below  is my desired outcome; I want to combine both rows where
--Column [1 Month] should always get data from Type1 CalculationType
--Column [2 Month] should always get data from Type2 CalculationType

SELECT 1 TestID,1 AS '1 Month', 5 AS '2 Month'

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2174 More actions · Answer 1

To make the selection criteria specific to each column you could try a conditional aggregation approach to pivoting the data

select TestID,
       max(iif(ReportingPeriod='1 Month' and CalculationType='Type1', Calculation, null)) _1_Month,
       max(iif(ReportingPeriod='2 Month' and CalculationType='Type2', Calculation, null)) _2_Month
from #Test
group by TestID;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

arnoldnashwel SSC Rookie Points: 30 More actions · Answer 2

The beauty of conditional aggregation lies in its flexibility and efficiency, particularly when working with large datasets and needing to avoid the overhead of multiple CTEs or temporary tables. By using the IIF function combined with MAX, you can specify conditions for each column directly within the aggregation, ensuring that your pivot aligns with the desired logic without the need for complex subqueries or additional staging steps.

Here's a slightly expanded explanation of the solution provided:max(iif(ReportingPeriod='1 Month' and CalculationType='Type1', Calculation, null)) as [1 Month]: This line checks each row in your dataset. If a row matches the reporting period of '1 Month' and has a calculation type of 'Type1', the calculation value is considered for aggregation; otherwise, it's treated as null for this specific condition.

max(iif(ReportingPeriod='2 Month' and CalculationType='Type2', Calculation, null)) as [2 Month]: Similarly, this line filters for '2 Month' reporting period and 'Type2' calculation type, aggregating only the relevant calculation values.

This method ensures that your pivot table output will have '1 Month' populated with Type1 calculations and '2 Month' with Type2 calculations, exactly as you need.

Chrissy321 SSCoach Points: 15768 More actions · Answer 3

Thank you both. Moving away from PIVOT and using the conditional aggregation method clearly gives me more flexibility to solve the problem and also seems to be performing better.

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 4

Another name for "conditional aggregation" is a CROSSTAB. MS use to carry a full entry in BOL on it until they came out with PIVOT. You'll be able to tell in the following old but still very pertinent article that I don't ever use PIVOT.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-converting-rows-to-columns-1

In case you like dynamic versions for things like "sliding windows" and dynamic entries, it's still simple...

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Chrissy321 SSCoach Points: 15768 More actions · Answer 5

Thanks for the links Jeff. I am going to put PIVOT in the 'do not use' receptacle where it can hang out with MERGE. 🙂

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 6

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)