June 5, 2008 at 8:09 am
Your Pivot is doing a grouping by propertyID AND usecode, which is wrecking your "alignment". pull the usecode and price out (leave them in the combined), and I think you'll get the output you want.
Sorry, Matt, but I don't understand this. Can you explain a bit more as to how this is grouping by propertyID and usecode, and/or where would I pull the usecode and price out?
June 5, 2008 at 8:32 am
Gwen (6/5/2008)
Your Pivot is doing a grouping by propertyID AND usecode, which is wrecking your "alignment". pull the usecode and price out (leave them in the combined), and I think you'll get the output you want.Sorry, Matt, but I don't understand this. Can you explain a bit more as to how this is grouping by propertyID and usecode, and/or where would I pull the usecode and price out?
A pivot is essentially a fancified version of a GROUP BY statement. Everything that is not specifically involved in the 2nd part of the PIVOT is used as a grouping element.
So in your case, you have ROW_NUM and COMBINEDDATA in the 2nd half of the pivot, which implicitly means that you're grouping by propertyID, useCode, price and frontage (missage the fontage last time). PropertyID is common to all of the pieces of land, but the others are unique, so your grouping isn't working.
I posted something several days ago that works (based on your initial CTE attempt), which doesn't use the "new" PIVOT command, which isn't great in this scenario, since it's too inflexible IMO.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 5, 2008 at 9:51 am
Gwen,
I forgot to post the results from my query, which is attached in Excel format. I'm fairly sure this is what you were after...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 5, 2008 at 1:10 pm
Gwen (6/4/2008)
Steve,By using the aggregate function on the COMBINED_DATA field, I'm
getting multiple rows per property, rather than one row per property.
Using the table #data Antonio posted earlier, the output is like:
Property Use_1 Price_1 Use_2 Price_2 Use_3 Price_3 (etc)
1 A 16000 null null null null
1 null null A1 7000 null null
1 null null null null A1 6000
What I wanted was:
Property Use_1 Price_1 Use_2 Price_2 Use_3 Price_3 (etc)
1 A 16000 A1 7000 A1 7000
...snip...
..Gwen..
the code i posted days ago does exactly that... 1 row per property.
[font="Courier New"]propertyId,frontage,useCode1,price1,useCode2,price2,...,useCode6,price6
1 175.00 A 16000.00 A1 7000.00 A1 6000.00
2 265.00 B 3000.00 A1 2000.00 A1 2000.00 B2 2000.00 B3 2000.00 B4 3500.00
3 125.00 A1 8000.00 A1 8000.00 [/font]
July 11, 2008 at 11:04 am
I'm finally getting the opportunity to work on this project again. Thank you so much for all your help.
Matt, your pivot explanation helped me understand where I was going wrong with this.
But a special thanks to Antonio. Your solution was what worked best. And thanks for
posting the sample code to create the table. I see now how much better it would have
been had I been able to post something like that initially.
..Gwen..
July 11, 2008 at 4:28 pm
I would like to find out more how to pivot because that seems more efficient than trying to manually manipulate the data. Do you always need to know what how many columns you will end up with? What if you can't know, like if you're trying to total up how many entries are made a on a day, are you suppose to make a bracket for every day in a month? If you know of any good references to look at to learn how to pivot I'd appreciate it.
Thanks!
(I've also had already started a new thread before i saw this one, if you prefer to reply to that one)
July 14, 2008 at 6:46 am
At least in SQL Server 2005, the PIVOT and UNPIVOT operators need to know the values they'll be dealing with, so if you don't know ALL the possible values, and it would be impractical to create a pre-determined list, then yes, you just don't have much of an alternative but to start working with such techniques as an auxiliary table of numbers (aka "tally table"). I can't tell you how many times using PIVOT and UNPIVOT have come in handy, so mastering the basic concept behind translating rows to columns and vice versa can make the difference between solving a problem or leaving it unsolved. There's also the self-join, which can be invaluable when it comes to handling data in a particular order, or in the pivot scenario. Combined with ROW_NUMBER() or a tally table, a self-join can do wonders. If you maintain a set-based way of thinking about your solution, you start to realize that you can accomplish 99.9999% of what you need without resorting to what Jeff Moden calls RBAR (aka "row by agonizing row"), and what I call "procedural thinking".
I remember when I first came across a mainframe-based language called SAS. It eliminated the need for any kind of loop by providing it for you automatically. All you had to do was specify the input data, and it would loop through it all for you, without you having to provide the looping code. Thus your only task was to provide any algorithm necessary to handle any one input record. Now that I think back on it, that's the same kind of thinking you need for T-SQL. Determine what works for any one input record, and you're largely good to go.
I don't know of any course material that could point you to more detail on how to approach the pivoting concept, but I suspect Jeff Moden may have some links that could be useful in that respect. Jeff?
Steve
(aka smunson)
:):):)
mblack (7/11/2008)
I would like to find out more how to pivot because that seems more efficient than trying to manually manipulate the data. Do you always need to know what how many columns you will end up with? What if you can't know, like if you're trying to total up how many entries are made a on a day, are you suppose to make a bracket for every day in a month? If you know of any good references to look at to learn how to pivot I'd appreciate it.Thanks!
(I've also had already started a new thread before i saw this one, if you prefer to reply to that one)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply