April 16, 2009 at 9:01 am
I have data in a table
Store Material Received_Date Qty.
East Capacitor 2008-03-11 1000
East Capacitor 2008-03-12 300
East Capacitor 2008-03-13 500
West Capacitor 2008-05-22 900
West Capacitor 2008-05-23 200
west Capacitor 2008-05-24 400
How to write t-sql to get output like ( the latest date first)
Store Material Received_Date1 Qty. Received_Date2 Qty. Received_Date3 Qty.
East Capacitor 2008-03-13 500 2008-03-12 300 2008-03-11 1000
West Capacitor 2008-05-24 400 2008-05-23 200 2008-05-22 900
April 16, 2009 at 9:07 am
Personally, I'd do that kind of pivoting in the application, not the database. You could do that in Reporting Services, or Crystal Reports, or Excel, very easily. In the database, you have to first figure out how many dates you want to show data for, and if that's variable, you pretty much have to use dynamic SQL to accomplish this. It can be done, but it opens up security issues that may not want to mess with.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2009 at 9:18 am
In addition to the Gsquard
Couple of questions:
1) Do you know how many records you are exepecting for each "Store Material"?
2) In your output "Qty" is 1000 i.e. Max Qty of each "Store Material"?
Do you have the option to use stored procedure? (this answer is based on Question 1)
April 16, 2009 at 9:36 am
To find similar threads where this question has been discussed, search SSC on the following keywords:
PIVOT
CROSSTAB
You do need to know the values you are going to turn into columns in advance, if it is an unknown number, then dynamic SQL is the only way to do it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 16, 2009 at 10:31 am
Check out the article from Jeff on Cross-Tab[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply