Extract one row to multiple rows

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

SQL Server 2005 Forums

Transact-SQL (2005)

Extract one row to multiple rows

Author

Topic

last
Starting Member

25 Posts

Posted - 2009-05-12 : 05:16:18

I am working on a Table called Approvals that has 2 columns Name,Group_Name
There are 3 GROUP_NAME(Finance,IT,Admin) split into three rows
but the in the Name colum each group can has multiple values seperated by a space for e.g.

GROUP_NAME-----NAME
Managers-------1001011 2762892 6368209 etc.
Admin----------8483909 0938950 3020348
IT-------------8483930

What I would like to do is do a select statement that puts each value in Name in it's own row e.g.
GROUP_NAME-----NAME
Managers-------1001011
Managers-------2762892
Admin----------8483909
Admin----------0938950
IT-------------8483930
etc.
I have done a select statemnt using substring function but it only returns the first value for each group. I figure i'll need a Loop of some sort or maybe there is a better solution.

SELECT NAME,SUBSTRING(GROUP_NAME, 0, 8) AS Initial
from Approvals
Order by Name

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-12 : 05:22:18

http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-12 : 05:31:45

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Splitting,delimited,lists

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-12 : 05:32:30

[code]
declare @str1 varchar(max)
set @str1= 'city country union club ultimate'

SELECT
SUBSTRING(@str1,charindex(' ',@str1,v.number),abs(charindex(' ',@str1,charindex(' ',@str1,v.number)+1)-charindex(' ',@str1,v.number)))as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND v.number <= len(@str1)
AND substring(' ' + @str1, v.number, 1) = ' '
[/code]

last
Starting Member

25 Posts

Posted - 2009-05-12 : 08:39:06

Thanks All for responding.
BKLR I ahd i have tried to implement your code. It works if i don modifications to it but when doing it to suit my need i get an error.
Msg 402, Level 16, State 1, Line 3
The data types text and varchar are incompatible in the equal to operator.

declare @str1 varchar(max)
SET  @str1 =(SELECT GROUP_NAME  from Approvaldefm1)
SELECT		
		SUBSTRING(@str1,charindex(' ',@str1,v.GROUP_NAME),abs(charindex(' ',@str1,charindex(' ',@str1,v.GROUP_NAME)+1)-charindex(' ',@str1,v.GROUP_NAME)))as value
FROM			Approvaldefm1 AS V
WHERE v.GROUP_NAME = 'P'
			AND v.GROUP_NAME > 0
			AND v.GROUP_NAME  <= len(@str1)
			AND substring(' ' + @str1 ,v.GROUP_NAME, 1) = ' '

I tried all sorts of gymnastic code with casts but i fail or get more errors.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources