sql query to return number of rows dynamically

Question

I have a table of form

 CREATE TABLE [dbo].[table1](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[clientid] [int] NULL,
[startdate] [int] NULL,
[copyid] [int] NULL

)

data in the table is of form:

 id clientid startdate  copyid
 1       4        11    1
 2       4        12    1
 3       4        44    2
 3       5       123    1
 4       5        15    1
 5       5        12    2
 6       5        12    2
 7       5        12    2

the copyid is subset of clientid

My question is that can i form a select query which returns a table with N number of rows and is a copy of clientid and copyid combination with copyid incremented.

For e.g. it should if clientid is taken as 4 and copyid as 1 and N as 6 it should return 6 rows like

  clientid startdate   copyid
  4        11           3
  4        12           3
  4        11           4
  4        12           4
  4        11           5
  4        12           5

N will always be a multiple of client and copy combination

I know how to do this using loops. But is it possible using a single select query?

What should be the value of column copy id in the final result? — Sonam
– Sonam, Commented Aug 26, 2013 at 19:43
Can you explain the logic that causes the CopyID in the output to start on 3? — Gidil
– Gidil, Commented Aug 26, 2013 at 19:43
And why is the 3rd row not part of the new output (where clientId = 4 and startdate = 44 (why is a date = 44???)? — Aaron Bertrand
– Aaron Bertrand, Commented Aug 26, 2013 at 19:45
There are two rows in the table with clientid 4 and copyid 1 so i want the query to increment the copyid after evry two rows and its base value should be max(copyid)+1 for that client — Siva Ramakrishnan
– Siva Ramakrishnan, Commented Aug 26, 2013 at 19:47
basically i want the clone of client and copy combination with copyid automatically incremented — Siva Ramakrishnan
– Siva Ramakrishnan, Commented Aug 26, 2013 at 19:49

Gidil · Accepted Answer · 2013-08-26 20:15:04Z

2

This can be accomplished using a simple cursor.

Using the sample data you gave in the question I created the following solution:

DECLARE @ClientID INT = 4 
DECLARE @CopyID INT = 1 
DECLARE @N INT = 6

;WITH DATA 
     AS (SELECT *, 
                Row_number () 
                  OVER ( 
                    ORDER BY ID)           RN, 
                Count(*) 
                  OVER ( 
                    PARTITION BY CLIENTID) CID 
         FROM   (SELECT *, 
                        Max(COPYID) 
                          OVER ( 
                            PARTITION BY CLIENTID) MaxID, 
                        0                          AS root 
                 FROM   TABLE1)T 
         WHERE  CLIENTID = @clientid 
                AND COPYID = @Copyid), 
     CTE 
     AS (SELECT * 
         FROM   DATA 
         UNION ALL 
         SELECT t2.[ID], 
                t2.[CLIENTID], 
                t2.[STARTDATE], 
                t2.[COPYID], 
                t2.MAXID, 
                t2.ROOT + 1, 
                t2.RN + T2.CID RN, 
                T2.CID 
         FROM   DATA t1 
                INNER JOIN CTE t2 
                        ON t1.ID = t2.ID 
         WHERE  t2.RN < @N - 1) 
SELECT CLIENTID, 
       STARTDATE, 
       MAXID + ROOT + 1 COPYID 
FROM   CTE 
WHERE  RN <= @N 
ORDER  BY COPYID

A working example can be found on SQL Fiddle.

answered Aug 26, 2013 at 20:15

Gidil

4,1472 gold badges38 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siva Ramakrishnan Over a year ago

Thanks Gidil. I was also thinking to use recursive query using CTE's but since I don't have a very good grip on cte's it would have taken me a very long time to come up with a solution. Thanks again

Collectives™ on Stack Overflow

sql query to return number of rows dynamically

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related