Transpose dynamic rows into column
-
hello all, i would like to seek your kind help regarding the transpose of rows into columns.
i have a table on the left hand side with more than 1000 company sales figure. I will use select distinct to pull each company from the table (sales), however i am having issue to populate each of the row (sales) into columns for easier review.
i would like to make a report for me to review those sales in a column format -15 columns (on the right hand side), can someone please kindly advice on how to build the TSQL for this?Thanks.
-
Do you have to do this in T-SQL? Pivoting a data in T-SQL can be quite a costly process, and if you require it to be dynamic, even more so (plus you have to use Dynamic SQL).
Presentation layers are much better at this. For example using a Matrix on SSRS, or a Pivot Table in Microsoft Excel. Would tools like these instead be a viable option for you? If so, and you're not sure how to use them, then just ask, and we'll be able to show you how. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
golden-1131024 - Sunday, May 7, 2017 4:09 AM
hello all, i would like to seek your kind help regarding the transpose of rows into columns.
i have a table on the left hand side with more than 1000 company sales figure. I will use select distinct to pull each company from the table (sales), however i am having issue to populate each of the row (sales) into columns for easier review.
i would like to make a report for me to review those sales in a column format -15 columns (on the right hand side), can someone please kindly advice on how to build the TSQL for this?Thanks.
This is actually pretty simple to do in T-SQL and it can have some very good performance in the presence of a thing known as "pre-aggregation" (tip o' the hat to Peter Larsson for the term and the method). Please see the following article.
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]--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)
Intro to Tally Tables and Functions
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply