How can I group record by 25 or any specified number per group?

I think this query will do the thing:

;with tt as (select *, row_number() over(order by val) as rn from t ),
cnt4 as (select count(*)/4 as c from t),
t1 as (select val, row_number() over(order by rn) as rn from tt cross join cnt4 where rn >=1 and rn <= cnt4.c),
t2 as (select val, row_number() over(order by rn) as rn  from tt cross join cnt4 where rn >=cnt4.c*1+1 and rn <= cnt4.c*2),
t3 as (select val, row_number() over(order by rn) as rn  from tt cross join cnt4 where rn >=cnt4.c*2+1 and rn <= cnt4.c*3),
t4 as (select val, row_number() over(order by rn) as rn  from tt cross join cnt4 where rn >=cnt4.c*3+1)
select 
t1.val as c1,
t2.val as c2,
t3.val as c3,
t4.val as c4
from
t1 right join t2 on t1.rn = t2.rn
   right join t3 on t3.rn = t2.rn
   right join t4 on t4.rn = t2.rn

Select allOpen in new window

Result:

|     C1 |     C2 |     C3 | C4 |
|--------|--------|--------|----|
|      1 |     11 |     21 | 31 |
|      2 |     12 |     22 | 32 |
|      3 |     13 |     23 | 33 |
|      4 |     14 |     24 | 34 |
|      5 |     15 |     25 | 35 |
|      6 |     16 |     26 | 36 |
|      7 |     17 |     27 | 37 |
|      8 |     18 |     28 | 38 |
|      9 |     19 |     29 | 39 |
|     10 |     20 |     30 | 40 |
| (null) | (null) | (null) | 41 |

Select allOpen in new window

Check the SQL Fiddle for example. Please provide your real table structure if you have troubles applying the pseudo code to your real situation

Looks great! I will try this and let you know.

Thanks.

chaau,

Can I have 2 fields per column?


Thanks.

What do you mean? do you mean you want total 8 columns with the pairs of columns?

Hi chaau,

My main objective is the report output having 2 fields per column.   It will look like this:

Colum 1            Column 2            Column 3            Column 4      
OR       Amt      OR       Amt            OR      Amt                   OR      Amt
123      100              136      500                    129      400                 132      250
124      1000       127      800                    130      450                 133      450
125      500             128      400                    131      5000         134      5000

I hope that makes sense.

Thanks.

Yes, it is possible. Check this up:

;with tt as (select *, row_number() over(order by ord) as rn from t ),
cnt4 as (select count(*)/4 as c from t),
t1 as (select ord, amt, row_number() over(order by rn) as rn from tt cross join cnt4 where rn >=1 and rn <= cnt4.c),
t2 as (select ord, amt, row_number() over(order by rn) as rn  from tt cross join cnt4 where rn >=cnt4.c*1+1 and rn <= cnt4.c*2),
t3 as (select ord, amt, row_number() over(order by rn) as rn  from tt cross join cnt4 where rn >=cnt4.c*2+1 and rn <= cnt4.c*3),
t4 as (select ord, amt, row_number() over(order by rn) as rn  from tt cross join cnt4 where rn >=cnt4.c*3+1)
select 
t1.ord as ord1, t1.amt as amt1,
t2.ord as ord2, t2.amt as amt2,
t3.ord as ord3, t3.amt as amt3,
t4.ord as ord4, t4.amt as amt4
from
t1 right join t2 on t1.rn = t2.rn
   right join t3 on t3.rn = t2.rn
   right join t4 on t4.rn = t2.rn

Select allOpen in new window

SQL Fiddle
Results:

|   ORD1 |   AMT1 |   ORD2 |   AMT2 |   ORD3 |   AMT3 | ORD4 | AMT4 |
|--------|--------|--------|--------|--------|--------|------|------|
|      1 |    234 |     11 |    234 |     21 |    234 |   31 |  234 |
|      2 |    234 |     12 |    234 |     22 |    234 |   32 |  234 |
|      3 |    234 |     13 |    234 |     23 |    234 |   33 |  234 |
|      4 |    234 |     14 |    234 |     24 |    234 |   34 |  234 |
|      5 |    234 |     15 |    234 |     25 |    234 |   35 |  234 |
|      6 |    234 |     16 |    234 |     26 |    234 |   36 |  234 |
|      7 |    234 |     17 |    234 |     27 |    234 |   37 |  234 |
|      8 |    234 |     18 |    234 |     28 |    234 |   38 |  234 |
|      9 |    234 |     19 |    234 |     29 |    234 |   39 |  234 |
|     10 |    234 |     20 |    234 |     30 |    234 |   40 |  234 |
| (null) | (null) | (null) | (null) | (null) | (null) |   41 |  234 |
		

Select allOpen in new window

chaau,

Seems to be looking great!

Now, this is the final challenge; I want to have parameters for a year and month, how can I do it?  


Thanks.

You should have provided the table structure you are working with. Would have been much easier. How is the date column called?

chaau,

I have a simple one as per attached.


Thanks.
structure.jpg

And the Count line as well and I think I am good to go:

cnt4 as (select count(*)/4 as c from t where [year]=@year and [month]=@month ),


Thanks.

Thanks chaau.  It's a great solution and nice working with you.

....or you can make the cnt4 CTE get the count from the tt CTE. Some reporting tools have troubles reusing the same parameters twice.

Please show me how " cnt4 CTE get the count from the tt CTE"?

Thanks.

cnt4 as (select count(*)/4 as c from tt ),

Got it - cnt4 as (select count(*)/4 as c from tt) !

Thanks.