SQL stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL stored procedure

Author

Topic

emanresu
Starting Member

3 Posts

Posted - 2009-03-03 : 09:37:04

I am working in SQL Server Management Studio 2008 and would like to perform a SELECT statement via stored procedure. For simplification purposes let us assume that the table has only three columns (Name, Type, City). The City column can have multiple entries separated by a comma. I would like to duplicate each row with multiple cities respectively. Here is an example:

Preceding SELECT:

Name        	Type     	City 
John Doe     	Owner		Las Vegas, Miami, New York

Following SELECT:

Name        	Type     	City 
John Doe	Owner		Las Vegas
John Doe	Owner		Miami
John Doe	Owner		New York

Any help would be greatly appreciated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-03 : 09:38:01

Read about UNPIVOT

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-03 : 09:40:41

UNPIVOT is for when you have multiple city columns (ie: city1, city2, etc.). In this case you need to use a split (or parsing) function. Search for that we have countless examples.

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 09:42:49

With the function fnParseList here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=120886

DECLARE	@Sample TABLE
	(
		Name VARCHAR(20),
		Type VARCHAR(20),
		City VARCHAR(200)
	)

INSERT	@Sample
SELECT	'John Doe', 'Owner', 'Las Vegas, Miami, New York'

-- Before
SELECT	*
FROM	@Sample

-- After
SELECT		s.Name,
		s.Type,
		LTRIM(RTRIM(f.Data)) AS City
FROM		@Sample AS s
CROSS APPLY	dbo.fnParseList(',', s.City) AS f

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 09:46:17

[code]
SELECT t.Name,
t.Type ,
f.Val
FROM Table t
CROSS APPLY dbo.ParseValues(t.City,',') f
[/code]
parsevalues can be found here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113563

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources