Need to create multiple rows from single row

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 2000 Forums

Transact-SQL (2000)

Need to create multiple rows from single row

Author

Topic

crackerbox
Starting Member

21 Posts

Posted - 2010-02-18 : 08:53:51

I have a table that has a field with a dollar amount and another field that has a list of items. I need to count the number of items in the list field and create the same number of rows, dividing the dollar amount by the number of items in the lista and distribute that cost to each item. The list length can vary. The separator is "&". Does anyone have any suggestions?

Example: Cost List
600 A & B & C

Final output needs to be

Cost List
200 A
200 B
200 C

Thanks
Sandy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 09:02:35

will the values be always seperated by & character?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

crackerbox
Starting Member

21 Posts

Posted - 2010-02-18 : 09:05:49

Yes from I have seen in the table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 13:14:52

[code]
CREATE TABLE #test
(Cost int,
List varchar(1000)
)
INSERT INTO #test
VALUES(200,'A & B & C & D & E'),
(700,'P & Q & R & S & T'),
(150,'M & N & O')

SELECT t.Cost/(LEN(List)-LEN(REPLACE(List,'&',''))-2),
SUBSTRING(List,v.Number+3, CHARINDEX('&', List, v.Number+3) -v.Number-3)
FROM (select Cost,' & ' + List + ' & ' as List from #test) t
CROSS JOIN master..spt_values v
where type='p'
and number BETWEEN 1 AND LEN(List)-3
AND SUBSTRING(List, v.Number, 3)=' & '

DROP TABLE #Test

output
-------------------------------
table data
--------------
Cost List
200 A & B & C & D & E
700 P & Q & R & S & T
150 M & N & O

final select
-----------------------------
Cost List
40 A
40 B
40 C
40 D
40 E
140 P
140 Q
140 R
140 S
140 T
50 M
50 N
50 O

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

crackerbox
Starting Member

21 Posts

Posted - 2010-02-19 : 13:44:32

This looks like it will work but I'm having difficulty getting the syntax correct when I try to change the script to insert into the table from the two fields instead of the "test" values. I'm sure this is a simple task for most but my scripting skills are pretty weak. What do I need to change in the script to insert into the table the two fields "cost" and "list".

Thanks
Sandy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 14:01:48

quote:
Originally posted by crackerbox

This looks like it will work but I'm having difficulty getting the syntax correct when I try to change the script to insert into the table from the two fields instead of the "test" values. I'm sure this is a simple task for most but my scripting skills are pretty weak. What do I need to change in the script to insert into the table the two fields "cost" and "list".

Thanks
Sandy

Post your attempted code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources