Convert rows into columns

Question

Convert rows into columns

Shree-903371

SSCommitted

Points: 1930
More actions
October 8, 2008 at 10:08 am

#213848

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?

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Shree-903371 SSCommitted Points: 1930 More actions · Answer 2

No, because this sp will be connected to crystal reports for creating report.

Garadin One Orange Chip Points: 29613 More actions · Answer 3

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.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Shree-903371 SSCommitted Points: 1930 More actions · Answer 4

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.

tverney Grasshopper Points: 18 More actions · Answer 5

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.

Shree-903371 SSCommitted Points: 1930 More actions · Answer 6

First I tried in cross tab itself but I have other fields like sales also. So its not giving the correct records.

John Rowan SSC Guru Points: 56440 More actions · Answer 7

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

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jerry Hung SSChampion Points: 12968 More actions · Answer 8

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..."

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Shree-903371 SSCommitted Points: 1930 More actions · Answer 9

Shree-903371

SSCommitted

Points: 1930

October 8, 2008 at 2:43 pm

#882588

Yes Jerry, we can do in that way.

Shree-903371 SSCommitted Points: 1930 More actions · Answer 10

Jerry can you help me out to arrange them in one column?

davidr-632841 Valued Member Points: 55 More actions · Answer 11

Side note: you do NOT want to be in Montreal when they find out you've moved them to Ontario .....

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 12

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

Shree-903371 SSCommitted Points: 1930 More actions · Answer 13

Hi, when I run the code , its giving me Incorrect syntax near XML.

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 14

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.