October 8, 2008 at 11:14 am
Hi,
I have two Columns State and City. When I select them from table, it displays like
STATE CITY
Connecticut Hartford
Connecticut Stamford
Connecticut New Haven
Connecticut Bridgeport
But I need them as
STATE CITY1 CITY2 CITY3 CITY4
Connecticut Hartford Stamford New Haven Bridgeport
So that I can use them in my SP. There are many cities for some states.
We are using SQL server 2005 but the datamart is in SQL server 2000, So i cant use 2005 functions like Pivot, Row_Number etc. We cant use dynamic sql at the place where i work. Can you please help me out?
October 8, 2008 at 12:18 pm
I once have the same need, I did it by a cumbersome stored procedure using cursor.
October 8, 2008 at 12:23 pm
Can you please help me with that code?
October 8, 2008 at 12:23 pm
October 8, 2008 at 12:28 pm
Can you please help me with that code?
Nothing special, just open a cursor and fetch record one by one and insert back to your new table.
October 8, 2008 at 12:30 pm
I will use that Stored Procedure for developing a report. In the report user need sales and some other requirements for that state with all the cities one per column.
October 8, 2008 at 12:32 pm
That's what I thought. This kind of work is best done by the reporting tool, whether it be crystal, SSRS, or some other tool.
October 8, 2008 at 12:35 pm
I’m not expert at SQL, so that’s why I’m asking for help. If you can atleast give me the code which you used then that will be of great help.
October 8, 2008 at 12:40 pm
Letting you know that it is best practice to do that kind of work in the reporting tool is helping you. That is what those types of tools were built for. If you want to do this in T-SQL within your stored procedure, it is going to take some skilled understanding of SQL. Since you've stated that you don't have that, you should consider using the reporting tool as suggested.
If you really want to code this, search SSC for dynamic pivots. You'll get a huge ton of examples of how to do this. Most of which will involve cursors and dynamic SQL and will drastically decrease the performance of your report.
If you want your report to run quickly and efficiently, don't burden the database server with this type of operation...use the reporting tool for what it is designed for.
October 8, 2008 at 12:48 pm
In the Crystal reporting tool also I tried which cannot convert one field into columns, it can convert all the fields which I use in the report or the detail section. So that’s why I decided to do this in the stored procedure itself. There is an other way called cross tab, which I already tried is not giving me the exact values.
I cannot use Pivot because the database is in SQL server 2000 and we are not allowed to use dynamic SQL. The only way may be with cursors.
October 8, 2008 at 1:25 pm
Before you get into it deeper, I would STRONGLY SUGGEST YOU NOT USING CRYSTAL REPORT.
There are just too many problems with this tool.
October 8, 2008 at 2:09 pm
Yes, I know. That’s why I’m trying in SQL by writing Stored Procedure.
October 8, 2008 at 2:27 pm
Please don't cross post....
Let's continue this thread here:http://www.sqlservercentral.com/Forums/Topic582739-338-1.aspx
October 8, 2008 at 2:29 pm
Ok, sorry about it.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply