Data display issue

Question

Post reply

Data display issue

Cnu2010

SSC Enthusiast

Points: 126
More actions
April 30, 2010 at 10:06 am

#220392

For a given County, I am returning the cities in that County
SELECT DISTINCT CITY FROM DEMO WHERE COUNTY = @COUNTY
CITY
-----
EDINA
BLOOMINGTON
EDEN PRAIRIE
SHAKOPEE
How do I display this data in a row like
CITY1 CITY2 CITY3 CITY4 CITY5 CITY6
--------------------------------------------------------------------------
EDINA BLOOMINGTON EDEN PRAIRIE SHAKOPEE
Thanks for great help
C

Viewing 9 posts - 1 through 8 (of 8 total)

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

GregoryF SSCertifiable Points: 6296 More actions · Answer 1

Write a cursor.

Fetch each county into it.

Create a variable and keep concat'ing the new county + a space

When the cursor is done, print the variable that contains the string with the individual rows

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

WayneS SSC Guru Points: 95405 More actions · Answer 2

Forget the cursor... try this instead.

-- See how this starts off with a table and data in it?

-- If you had provided us the data in this format,

-- it would have made things easier for all of the

-- volunteers on this site to help you out.

DECLARE @test-2 table (Country varchar(50), City varchar(50))

INSERT INTO @test-2

SELECT 'USA','Washington' UNION ALL

SELECT 'USA', 'Portland' UNION ALL

SELECT 'USA', 'San Antonio' UNION ALL

SELECT 'USA', 'Houston' UNION ALL

SELECT 'USA', 'Tampa' UNION ALL

SELECT 'USA', 'Boston'

;WITH CTE AS

(

SELECT Country,

RowID = ROW_NUMBER() OVER (ORDER BY (SELECT 0)),

City

FROM @test-2

WHERE Country = 'USA'

)

SELECT City1 = MAX(CASE WHEN RowID = 1 THEN CITY ELSE NULL END),

City2 = MAX(CASE WHEN RowID = 2 THEN CITY ELSE NULL END),

City3 = MAX(CASE WHEN RowID = 3 THEN CITY ELSE NULL END),

City4 = MAX(CASE WHEN RowID = 4 THEN CITY ELSE NULL END),

City5 = MAX(CASE WHEN RowID = 5 THEN CITY ELSE NULL END)

FROM CTE

You might want to check out this article by MVP Jeff Moden[/url] on generating dynamic cross-tab data.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

How the heck will that work with a country like USA or Canada where you have 1000s of cities?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Cnu2010 (4/30/2010)
For a given County, I am returning the cities in that County
SELECT DISTINCT CITY FROM DEMO WHERE COUNTY = @COUNTY
CITY
-----
EDINA
BLOOMINGTON
EDEN PRAIRIE
SHAKOPEE
How do I display this data in a row like
CITY1 CITY2 CITY3 CITY4 CITY5 CITY6
--------------------------------------------------------------------------
EDINA BLOOMINGTON EDEN PRAIRIE SHAKOPEE
Thanks for great help
C

What's the big picture of the project here? This seems like a reporting need rather than simple DML.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

GregoryF (4/30/2010)
Write a cursor.
Fetch each county into it.
Create a variable and keep concat'ing the new county + a space
When the cursor is done, print the variable that contains the string with the individual rows

Heh... nah... no cursors... too slow. Besides, the OP wants it in separate columns.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Ninja's_RGR'us (4/30/2010)
How the heck will that work with a country like USA or Canada where you have 1000s of cities?

Heh... one can only hope that the criteria presented is "example only" and would be a little tighter. 😉

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Cnu2010 (4/30/2010)
For a given County, I am returning the cities in that County
SELECT DISTINCT CITY FROM DEMO WHERE COUNTY = @COUNTY
CITY
-----
EDINA
BLOOMINGTON
EDEN PRAIRIE
SHAKOPEE
How do I display this data in a row like
CITY1 CITY2 CITY3 CITY4 CITY5 CITY6
--------------------------------------------------------------------------
EDINA BLOOMINGTON EDEN PRAIRIE SHAKOPEE
Thanks for great help
C

You're almost brand new to the forum... take a look at the article at the first link in my signature line below. People will trip over each other to help you with a coded example...

--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)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 8

DECLARE @test-2

TABLE (

Country VARCHAR(50),

City VARCHAR(50)

);

INSERT @test-2

SELECT 'USA', 'Washington' UNION ALL

SELECT 'USA', 'Portland' UNION ALL

SELECT 'USA', 'San Antonio'UNION ALL

SELECT 'USA', 'Houston' UNION ALL

SELECT 'USA', 'Tampa' UNION ALL

SELECT 'USA', 'Boston' UNION ALL

SELECT 'NZ', 'Wellington' UNION ALL

SELECT 'NZ', 'Auckland' UNION ALL

SELECT 'NZ', 'Christchurch'UNION ALL

SELECT 'NZ', 'Dunedin';

SELECT P.Country,

P.City1,

P.City2,

P.City3,

P.City4,

P.City5,

P.City6,

P.City7,

P.City8,

P.City9

FROM (

SELECT T.Country,

value = T.City,

name = 'City' + CONVERT(VARCHAR(20), ROW_NUMBER() OVER (PARTITION BY T.Country ORDER BY (SELECT 0)))

FROM @test-2 T

) Source

PIVOT (

MAX(value)

FOR name IN (City1, City2, City3, City4, City5, City6, City7, City8, City9)

) P;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi