Single row to multiple rows based on column value

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

Transact-SQL (2008)

Single row to multiple rows based on column value

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-08-03 : 10:05:15

I am trying to break single records into multiple records based on a value from one of the columns.

Basically, each row has an 'Items' column.
How can I break each row into multiple rows based on the number found in the 'Items' column.

Table structure below:

Original Table Values

Name, Items

AAA, 1
BBB, 2,
CCC, 3

Desired New Table Values

Name, Items

AAA, 1
BBB, 1,
BBB, 1,
CCC, 1
CCC, 1
CCC, 1

Much Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-03 : 10:39:19

You can use any Numbers table or function to accomplish generating rows. Here is an example using your sample data.
This code assumes [Items] will start with one numeric character that can be converted to INT as in your sample data.


declare @t table (name varchar(10), items varchar(10))
insert @t 
select 'AAA', '1' union all
select 'BBB', '2,' union all
select 'CCC', '3'

select t.name
       ,'1' + substring(items, 2, 1000) as Items
from   @t t
join   master..spt_values n
       on n.type = 'P'
       and n.number < convert(int, left(items,1))

name       Items
---------- -----------
AAA        1
BBB        1,
BBB        1,
CCC        1
CCC        1
CCC        1

Be One with the Optimizer
TG

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-08-03 : 11:33:06

Thanks TG, I am not sure that is really where I want to go.

I am trying for something like this:

select name,
while (items > 0 and counter < items) insert '1', othersise insert '0' into #temp from dbo.live

dbo.live
name item
a 2
b 0
c 3

desired temp table
a 1
a 1
b 0
c 1
c 1
c 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources