quote:

---

Originally posted by mapradeepkumar

Hi
I have a table
ID RollNo FeeHead Amount
1 1001 234 1000
2 1001 224 1100
3 1001 234 1200
4 1001 235 1300
5 1002 234 1000
6 1002 224 1100
7 1002 234 1200
8 1002 235 1300
9 1002 11 150
10 1003 234 1000
11 1003 224 1100
12 1003 234 1200
13 1003 235 1300
14 1003 11 150
15 1003 10 250

Now i need total 7 rows per rollno irrespective of number of rows it has
ie total 7 records(record count) should be there
RollNo RecordCount
1001 4
1002 5
1003 6
As 1001 has only 4 record count
so i should be able to add another 3 records to make it 7
the other columns can have null values.
i can do this using a loop but i don't want to use a loop, can any one suggest a way to do this in sql2000 or sql2005

Thanks in Advance

---

Insert into table (RollNo,FeeHead,Amount)
SELECT t.RollNo,NULL,NULL
FROM (SELECT RollNo,COUNT(*) AS Rows FROM table GROUP BY RollNo) t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND v.number BETWEEN 1 AND 7
AND v.number>t.Rows