Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have table data like

data1
data2
data3
data4

and I need to select these rows like this:

data1
data1
data1
data1
data2
data2
data2
data3
data3

and how to perform this in SQL Server 2005?

share|improve this question

closed as not a real question by ypercube, dezso, Mark Storey-Smith, Max Vernon, Kin Jun 24 '13 at 14:29

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

    
What is the need for this? Your application will need to pass in information such as 4,data1, 4,data2, 1,data3 to tell SQL Server how many rows to return. So it might as well not bother involving SQL Server at all and just create the collection itself. –  Martin Smith Jun 24 '13 at 11:19
    
@Martin Smith you got the point...thats what i want and please explain with example how to do it? and i need the output must single table datas...that is important –  Suresh E Jun 24 '13 at 11:32
    
Please don't delete your questions and re-ask. People will still reply to the original one even if it's several hours old. –  Jon Seigel Jun 24 '13 at 16:35
add comment

1 Answer 1

up vote 1 down vote accepted

You haven't explained why you need SQL Server to do this multiplying out of rows rather than your application. You are quite possibly asking the wrong question.

You can do this With an auxiliary table of numbers though.

CREATE TABLE Numbers (N INT PRIMARY KEY)

INSERT INTO Numbers
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values v1, master..spt_values v2

and then

SELECT T.YourColumn
FROM   YourTable T
       INNER JOIN Numbers N
         ON ( T.YourColumn = 'data1' AND N.N <= 4 )
             OR ( T.YourColumn = 'data2' AND N.N <= 4 )
             OR ( T.YourColumn = 'data3' AND N.N <= 1 ) 
share|improve this answer
    
sorry i forgot to mention that....these nooftimes will send from application and the server need to give output like the above....is your current answer will work for that? need to get nooftimes from application...please help –  Suresh E Jun 24 '13 at 11:59
    
@SureshE - Well your application would need to generate a parameterised version of the query above with as many OR conditions as required or use one of the methods in Arrays and Lists in SQL Server 2005 to pass in these values. As I've already commented I don't see why you are trying to do this in SQL at all. You should probably ask a question on StackOverflow (as off topic here) as to how to achieve what ever it is you are trying to do at the application level. –  Martin Smith Jun 24 '13 at 12:06
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.