Please help !!!

Hi, i have a problem about t-sql.

Table is with data

ID Name Part_Count

- --

1 Bush 3

2 Blair 2

3 Erdogan 2

Result table must be

ID NAME PART PART_COUNT

- - --

1 BUSH 3 1

2 BUSH 3 2

3 BUSH 3 3

4 BLAIR 2 1

5 BLAIR 2 2

6 ERDOGAN 2 1

7 ERDOGAN 2 2

HOW CAN I DO ?

The query below uses a couple of tricks. In order to expand the original part_count into a matching number of rows, you need a source of numbers stored in rows. Spt_values is a system table that has such a source of numbers. You could also build your own table or a table function.

Second, to generate the id notice that the original table can be used to get the starting id for the final list by summing the part_count field of all the rows with lower ids.

I have included my work tables so that you will have a complete example.

If this is for an assignment, I'd bet that you are supposed to use a cursor. That solution you ought to be able to figure out on your own.

/*Create table #one(
pid int not null Identity(1,1),
pname varchar(10) not null,
part_count int not null
)

insert #one values( 'Bush', 3 )
insert #one values( 'Blair', 2 )
insert #one values( 'Erdogan', 2 )
*/

Select id = (
Select start = IsNull( Sum( p2.part_count ), 0 )
From #one p2
Where p2.pid < parts.pid
) + v.number + 1,
name = parts.pname,
part = parts.part_count,
part_count = v.number + 1
From #one parts,
master..spt_values v
Where v.type = 'P'
And v.number < parts.part_count