Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

How to Pivot without an aggregate Expand / Collapse
Author
Message
Posted Tuesday, April 26, 2011 11:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 26, 2011 10:59 AM
Points: 1, Visits: 12
Thanks you for this example! You saved me some time since I was able to modify for a similar Pivot without aggregation.
Post #1098825
Posted Thursday, December 26, 2013 11:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 11:57 AM
Points: 39, Visits: 147
Thank you so much for your code. I have been looking for two days for someone to list a simple example of how to "pivot" rows and columns WITHOUT aggregates. I know its not truly a pivot in the SQL/Excel point of view, but I still feel the terminology holds up as it is turning row data into columnar data. I have seen many bad examples; and almost all of them aggregate or have a fixed number of columns. Yours is the only one with the three elements I needed, dynamic columns, change of row to column, and NO- Aggregation. I know I can't be the only one who has to come up with lists like say customer/part no. where you want jus the list of customer once and the part no. in columns out to the right but the data is formatted to be 1 to 1 customer/part no. which repeats customers over and over.

Thank you again.
Post #1526015
Posted Thursday, December 26, 2013 1:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 35,584, Visits: 32,174
asheppardwork (12/26/2013)
Thank you so much for your code. I have been looking for two days for someone to list a simple example of how to "pivot" rows and columns WITHOUT aggregates. I know its not truly a pivot in the SQL/Excel point of view, but I still feel the terminology holds up as it is turning row data into columnar data. I have seen many bad examples; and almost all of them aggregate or have a fixed number of columns. Yours is the only one with the three elements I needed, dynamic columns, change of row to column, and NO- Aggregation. I know I can't be the only one who has to come up with lists like say customer/part no. where you want jus the list of customer once and the part no. in columns out to the right but the data is formatted to be 1 to 1 customer/part no. which repeats customers over and over.

Thank you again.


Just so you know, MAX is considered to be a form of an "Aggregate". I wanted to clarify that because when people ask me how to pivot without an aggregate in T-SQL, I tell them it's not possible... you need an aggregate in on form or another.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526032
Posted Friday, December 27, 2013 3:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 1:04 PM
Points: 168, Visits: 109
create table CaseCode(
caseID int, Code varchar(10))
insert into CaseCode values
(1 ,'AAA')
,(1 ,'BBB')
,(1 ,'CCC')
,(1 ,'DDD')
,(2 ,'CCC')
,(3 ,'AAA')
,(3 ,'BBB')
,(3 ,'CCC')
,(3 ,'DDD')
,(3 ,'EEE')

select * from CaseCode

select caseID, AAA as code1,BBB as code2,CCC as code3,DDD as code4 from
(select caseid,code from CaseCode)
a pivot
(max(code) for code in (AAA,BBB,CCC,DDD))AS pivo
order by caseID
Post #1526104
Posted Friday, December 27, 2013 7:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 11:57 AM
Points: 39, Visits: 147
Thank you for the clarification, I was aware that MAX is an aggregate; but you use it in such a way as to allow each row to be its own max and hence getting results for every row. I was excited yesterday because the longer I searched the farther away I got from the answer. It seems that it is hard for most SQL users to believe there are legitimate reasons for needing a long list of items with multiple instances to be made into a short list in SQL. Its almost as if no one else does ERP type reporting with quarterly figures that compare year over year figures for a variable and constantly changing group of people; which I know is not true.

One example, my own, is that I am producing a SSRS report in which I need to show a salespersons name and then the id next to it. However in Microsoft's Dynamics GP they allow sales people to have multiple ids; so "Jim Smith" can have ids "1234,1234z,1245x,1264" and if you need a list without the person being listed four times; you can have SSRS do the work or SQL. The rub comes in when you have 100+ sales people and each with a combination of between 1 and 9 ids and no control over whether or not to clean up the data or if the ids have a set pattern. Now your looking at just for two fields returning over 800+ rows and then having SSRS do the logic at runtime. The overall effect is a slow report that at best must be cached each night. Now say that management requires said report to be able to run ad-hoc during the production day with the latest information. Suddenly all the answers of SQL shouldn't be doing this or let SSRS handle it etc. sound academic and rather unhelpful. That is where your code comes in, now I can add it to my rather lengthy Stored Proc. and return just 100 records with name and id(s) at the beginning followed by other un-related data and the report runs quickly and quietly with no matrix pivot; sub-report; mish-mash for the reporting engine to translate before producing results.

In the future, I will be searching here first; for whatever reason sql server central does not get very good results in google or duckduckgo for specifics only on the generalities.
Post #1526135
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse