T-SQL Pivot Question

Question

T-SQL Pivot Question

YSLGuru

SSC-Insane

Points: 21632
More actions
May 5, 2022 at 4:09 pm

#4027485

I am familiar with T-SQL's Pivot Operator, I've used it to convert rows of financial data into columns like one would find in a 12 Month Income Statement. What I've never tried before and am not sure if its even possible is using Pivot when the end results will have 2 (not just 1) set of columns; is that even doable?

Imagine an Excel spreadsheet sheet with 144 rows of financial data . Each row contains a period (between Jam 2022 and Dec 2022) , an Account Number & name and an amount. This is pivoted so that you have 12 columns (jan 2022 - Dec 2022) with the Account Name & Number on the left going down and intersecting in each row & column is a financial amount corresponding to the Account on the left and the Period at the top. I've done this exact thing using Pivot.

Now imagine this same 12 Month Income Statement will include an addiotnal row underneath the row that contains the periods. This row will specify what set of Financial books each value is for and there are 3 books: Cash, Accrual & IFRS. Instead of their being 144 rows there are no 144 * 3 (because the 3 books versus just the assumed 1 before) and the pivoting of this data must now do for the row containing the Periods the same thing for the row beneath that will now contain the books. Instead of the end result being 13 columns of data (1 column for the Account and 12 columns for the 12 periods in the year) there will now be 1+ 12*3 for 37 columns.
Is this kind of thing possible with the Pivot operator in T-SSQL? If so and you want tp provide an example that's great I'm just happy to know if it is and if so what is the proper term I need to use to find articles & example on how to do this with pivot. Knowing the proper search terms when looing for something is often harder then finding the answer.

Thanks
Kindest Regards,
Just say No to Facebook!

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

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

Y.B. SSChampion Points: 11642 More actions · Answer 1

I think many of us here would be happy to provide an example if you provided some DDL we could work from. However, if you really just want some research suggestions I would say look for "Crosstab Queries". You don't actually need to use the PIVOT operator to pivot data in SQL. I personally haven't used it in years...

SELECT quote FROM brain WHERE original = 1
0 rows returned

YSLGuru SSC-Insane Points: 21632 More actions · Answer 2

Thank you for replying. After posting this I tried searching via google for the answer and to my surprise the search terms I used were correct, somethin that rarely happens for me. Using "how to pivot multiple columns to rows in SQL server" actually worked. I am accustom to there need to be some specific key word one needs to get the answer, a term that's different from I would assume, more often than not its jingo specific to the topic that those not familiar with it would not know.

I didn't post any code because our DB is cloud based and there doing some kind of switch over so I am unable to access it like normal for today and possibly tomorrow. I took this down time to look into this as it recently came up.

Thanks

Kindest Regards,

Just say No to Facebook!

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 3

Just so you are aware - if you have decided to use the 'many' suggestions of adding multiple PIVOT statements to your query, you are going to find out (quite soon I would say) that this is not something that performs very well.

Now, if you happened to find the great articles on this site about CROSSTABS (starting here: https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-converting-rows-to-columns-1) and are using that technique, then great - if not, I suggest you take a look.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

YSLGuru SSC-Insane Points: 21632 More actions · Answer 4

Will do. Currently the report that uses the existing non-pivot query takes up to 5 minutes to render on screen even though the source query it uses completes within a second. Its due to the horrendous SSRS reporting engine. It appears to not handle pivots well at all. If the Pivot operator with multiple columns takes 10X longer than the current non-pivot query it will still be a faster report then what we have now as then we will be able to move away from using SSRS for the pivoting.

Thanks

Kindest Regards,

Just say No to Facebook!

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 5

I haven't found that the SSRS matrix is any slower or faster than doing the work in SQL Server. In fact, I have found that performing the pivot in SSRS is more flexible because it can be dynamic. In general, when I have seen issues with the matrix it is because the matrix has to perform too many calculations to achieve the end results - which almost always comes down to the detail data that is being used.

For those situations I create a query that pre-aggregates the data so the only thing the matrix needs to do is pivot the data. That way SSRS isn't doing a lot of work aggregating and pivoting - just pivoting the data for display.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs