Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Pivot without an aggregate


How to Pivot without an aggregate

Author
Message
JMagargal
JMagargal
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 14
Thanks you for this example! You saved me some time since I was able to modify for a similar Pivot without aggregation.
asheppardwork
asheppardwork
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 162
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.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45066 Visits: 39904
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
amrut.kumbar 7070
amrut.kumbar 7070
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 150
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
asheppardwork
asheppardwork
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 162
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search