Add additional column to dataset based on rowcount

Question

Post reply

Add additional column to dataset based on rowcount

subasuga

Mr or Mrs. 500

Points: 510
More actions
October 4, 2011 at 3:28 am

#259895

I have a table like this
1 AAAA
2 BBBB
3 CCCC
...
100 FFFF
I want to add additional column and fill in data, based on the row count; (ie) for the first five rows i want to write as Sample1. for the next 5 as sample 2 and so on...
Like this...
1 AAAA Sample1
2 BBBB Sample1
3 CCCC Sample1
4 DDDD Sample1
5 EEEE Sample1
6 GGGG Sample2
......
100 FFFF Sample20
Any suggestions??

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 1

Try the NTILE function:

http://msdn.microsoft.com/en-us/library/ms175126.aspx

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

subasuga Mr or Mrs. 500 Points: 510 More actions · Answer 2

NTILE just divides the dataset into the given number in NTILE(int) accordingly. But i want to divide the dataset (whatever lines there may be) to 5 - 5 rows..

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 3

subasuga (10/4/2011)
NTILE just divides the dataset into the given number in NTILE(int) accordingly. But i want to divide the dataset (whatever lines there may be) to 5 - 5 rows..

I see.

This solution is pretty close:

SELECT

[SequenceNumber]

,[SampleColumn] = 'Sample' + CONVERT(VARCHAR(10),([SequenceNumber] / 5) + 1)

FROM MyTable

Row 5 belongs to sample 2, while your example puts it in sample1.

Maybe add a check with the modulo operator to check if the current number can be divided by 5. If it is, substract 1.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

subasuga Mr or Mrs. 500 Points: 510 More actions · Answer 4

Hi..

Thanks for thea bit of useful tip.

I modified the query like this and the rows getting divided into 5 perfectly...

SELECT

[SequenceNumber]

,[SampleColumn] = 'Sample' + CONVERT(VARCHAR(10),(([SequenceNumber] / 5)-1) + 1)

FROM MyTable

Thanks Koen..