Solved

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

Posted on 2014-07-16
154 Views
Dear Experts,

I am trying to generate a multi-columns sub reports, but it is unachievable with my Reporting tool for now, and my option is to use tables with their respective data sources. With this, I need to break my record into 4 columns by a number of records depending on the total number of records.

Is it possible to use pivot for the this scenario? I have only 2 fields reports that ranges from 50-150 records per monthly reporting.

Your best solution will be highly appreciated.

Thanks.
0
Question by:JimiJ13

LVL 24

Expert Comment

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
``````
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 |
``````
Check the SQL Fiddle for example. Please provide your real table structure if you have troubles applying the pseudo code to your real situation
0

Author Comment

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

Thanks.
0

Author Comment

chaau,

Can I have 2 fields per column?

Thanks.
0

LVL 24

Expert Comment

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

Author Comment

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.
0

LVL 24

Expert Comment

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
``````
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 |

``````
0

Author Comment

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.
0

LVL 24

Expert Comment

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

Author Comment

chaau,

I have a simple one as per attached.

Thanks.
structure.jpg
0

LVL 24

Accepted Solution

Just add the parameters to the first CTE on the first line, like this
;with tt as (select *, row_number() over(order by ord) as rn from t where [year]=@year and [month]=@month ),

The rest of the query doesn't need any modifications
0

Author Comment

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.
0

Author Closing Comment

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

LVL 24

Expert Comment

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

Author Comment

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

Thanks.
0

LVL 24

Expert Comment

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

Author Comment

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

Thanks.
0

## Featured Post

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, â€¦
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to alâ€¦
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
This video is in connection to the article "The case of a missing mobile phone (https://www.experts-exchange.com/articles/28474/The-Case-of-a-Missing-Mobile-Phone.html)". It will help one to understand clearly the steps to track a lost android phone.