October 8, 2008 at 10:08 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 10:22 am
ouch...without dynamic sql, and still trying to do it in SQL and not a programming language, I don't know how you'd be able to do it.
the headache is the max() number of cities beneath a state...your database might currently have 4 cities for Connecticut as an example, but potentially you might need a column for each city.. ~270 cities for Connecticut. add a different state, and you might need more columns . because that value is potentially dynamic based on data input of new cities on a daily basis, you either need to assume a maximum number of columns, or build the view with dynamic sql...which is against your limitations.
it's easy to create what you need in a programming language after gathering the data from sql server, but without dynamic sql, I'm not sure it's possible. just creating a grid and populating it from a loop.
are you allowed to use a CLR to call an external program that would create the view dynamically? so the programming moves off of SQL and into CLR?
Lowell
October 8, 2008 at 10:29 am
No, because this sp will be connected to crystal reports for creating report.
October 8, 2008 at 1:51 pm
Not sure if this would work as I haven't done much cross version stuff yet, but couldn't you select the data you need into a temp table on your SQL 2K5 Server before processing? Ordinarily you'd probably avoid this, but if it saves you doing something crazy with loops or cursors over a SQL 2000 method, it'd probably be worth it... if it worked.
October 8, 2008 at 2:07 pm
As the datamart is connected to management Studio, when Iām running it does not allow. So Iām unable to copy to temp table also.
October 8, 2008 at 2:14 pm
If you're using Crystal, then the crosstab report may be an option. Formatting in a crosstab report is poor, paper foot print may be an issue but being handicapped by no dynamic sql and no 2005 functions, you're options are limited.
October 8, 2008 at 2:27 pm
First I tried in cross tab itself but I have other fields like sales also. So its not giving the correct records.
October 8, 2008 at 2:28 pm
Here's a link to the OP's other post on this same topic for reference, but let's use this current thread as the only one going forward.
Other thread:http://www.sqlservercentral.com/Forums/Topic582789-8-1.aspx
October 8, 2008 at 2:31 pm
I don't see a quick elegant solution
CURSOR will be the quickest
PIVOT may be harder with UNKNOWN # of Cities/columns per row (may involve Dynamic SQL)
Can you put the Cities into a CSV in 1 column?
like
"Ontario", "Toronto, Mississauga, Montreal, etc..."
October 8, 2008 at 2:43 pm
Yes Jerry, we can do in that way.
October 9, 2008 at 9:02 am
Jerry can you help me out to arrange them in one column?
October 10, 2008 at 7:45 am
Side note: you do NOT want to be in Montreal when they find out you've moved them to Ontario .....
October 10, 2008 at 11:35 am
Shree (10/9/2008)
Jerry can you help me out to arrange them in one column?
This would get the CSV
DECLARE @Cities TABLE
(
State VARCHAR(20)
,City VARCHAR(20)
)
INSERT INTO @Cities
SELECT 'Connecticut','Hartford'
UNION ALL SELECT 'Connecticut','Stamford'
UNION ALL SELECT 'Connecticut','New Haven'
UNION ALL SELECT 'Connecticut','Bridgeport'
SELECT State
,STUFF(
(
SELECT ', ' + B.City
FROM @Cities B
WHERE A.State = B.State
FOR XML PATH(''))
,
1, 2, '')
FROM @Cities A
GROUP BY State
October 13, 2008 at 9:01 am
Hi, when I run the code , its giving me Incorrect syntax near XML.
October 13, 2008 at 9:11 am
Shree (10/13/2008)
Hi, when I run the code , its giving me Incorrect syntax near XML.
That's strange. I copied & pasted the code straight from the forum. It's not giving me an error.
Are you having trouble with this exact code? Or are you having trouble applying it to your scenario?
If it is the latter, post the exact code you ran.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply