Data like Crosstab

Question

Data like Crosstab

Shree-903371

SSCommitted

Points: 1930
More actions
October 22, 2009 at 10:09 am

#134700

I currently have data in this format:
StateCityCodeName
CT01Hartford
CT02New Haven
CT03Stamford
VA01Herndon
VA02Reston
I need them in this format (like Crosstab):
StateCityCode1Name1 CityCode2Name2CityCode3 Name3
CT01Hartford02New Haven03 Stamford
VA01Herndon02Reston
I was thinking to use pivot but as we use SQL server 2000, I’m unable to use it.
I can limit the max CityCode and Name to 20 columns. Like CityCode1 … CityCode20.
So anybody can help me with it? I was thinking about cursor, but I’m not familiar with it.
SELECT
State,
CityCode,
Name
FROM tblAddress
WHERE State IN ('CT','VA')
ORDER BY 1,2

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Check out the 2 Cross Tab and Pivot articles linked in my signature. They give a better explanation than I could. They do apply to SQL Server 2000.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Agreed... no need for a Cursor... see the two articles that Jack pointed you to... I know the guy that wrote them. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Thank you. While I was working after reading the article, my user suggested she wants it in CSV format. Which I can manage.

I know I may get these kinds of requests in future, so I will make a note of these articles.

Once again thank you Jack and Jeff.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Hmmmm... CSV format... that's NOT a cross-tab. That's concatenation. Here's another article link. This one shows a couple of methods of concatenation and some of the associated pitfalls with performance. I know this guy pretty well, too! 😛

http://www.sqlservercentral.com/articles/Test+Data/61572/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)