August 22, 2008 at 2:58 pm
I have data in a table as shown below (Before) that I need to use t-SQL to dynamically transpose/format into the format in (After) below. Any assistance will be appreciated.
Thanks.
Before:
AccountDOS
45732/27/2006
84895/31/2007
63641/19/2006
63642/13/2006
635310/24/2006
63538/20/2007
635311/27/2006
63532/5/2007
63533/27/2007
63531/4/2007
57512/12/2007
45679/27/2006
45672/8/2007
45677/5/2007
45673/20/2007
45673/21/2006
45672/20/2007
45676/13/2006
87423/14/2007
87428/2/2007
87422/22/2007
After:
AccountDOS1DOS2DOS3DOS4DOS5DOS6
45732/27/2006
84895/31/2007
63641/19/20062/13/2006
635310/24/20068/20/200711/27/20062/5/20073/27/20071/4/2007
57512/12/2007
45679/27/20062/8/20077/5/20073/20/20073/21/20062/20/20076/13/2006
87423/14/20078/2/20072/22/2007
August 22, 2008 at 3:06 pm
How are you determining where each DOS falls? Is it strictly based upon earlier to later? What version of SQL Server are you using?
Please review the article: Best Practices: http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Post back with the create statements, insert statements and desired results as outlined in the referenced article.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 22, 2008 at 3:22 pm
I'm currently using SQL Server 2000, but I have access to 2005 if needed. The DOS the dates fall into should be in earlier to later order (though not in my example).
Thanks.
August 22, 2008 at 3:46 pm
You can review the following articles to get an idea of how to get this done:
Cross-Tabs: http://qa.sqlservercentral.com/articles/T-SQL/63681/
Tally Table: http://qa.sqlservercentral.com/articles/TSQL/62867/
In SQL Server 2005 you can use the row_number() function and OVER windowing function to identify each date by number and then cross-tab that using the standard cross-tab in the above article or the PIVOT statement.
For SQL Server 2000 - you would use the Tally table to do the same thing as the row_number().
For additional help, please refer to the article in my previous post and take the time to setup the create statements, insert statements and desired results. With that it will be a lot easier for someone to build you a working (and tested) query.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply