• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

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

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
JimiJ13
Asked:
JimiJ13
  • 9
  • 7
1 Solution
 
chaauCommented:
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

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

Open 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
0
 
JimiJ13I T ConsultantAuthor Commented:
Looks great! I will try this and let you know.

Thanks.
0
 
JimiJ13I T ConsultantAuthor Commented:
chaau,

Can I have 2 fields per column?


Thanks.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
chaauCommented:
What do you mean? do you mean you want total 8 columns with the pairs of columns?
0
 
JimiJ13I T ConsultantAuthor Commented:
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
 
chaauCommented:
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

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

Open in new window

0
 
JimiJ13I T ConsultantAuthor Commented:
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
 
chaauCommented:
You should have provided the table structure you are working with. Would have been much easier. How is the date column called?
0
 
JimiJ13I T ConsultantAuthor Commented:
chaau,

I have a simple one as per attached.


Thanks.
structure.jpg
0
 
chaauCommented:
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
 
JimiJ13I T ConsultantAuthor Commented:
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
 
JimiJ13I T ConsultantAuthor Commented:
Thanks chaau.  It's a great solution and nice working with you.
0
 
chaauCommented:
....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
 
JimiJ13I T ConsultantAuthor Commented:
Please show me how " cnt4 CTE get the count from the tt CTE"?

Thanks.
0
 
chaauCommented:
cnt4 as (select count(*)/4 as c from tt ),
0
 
JimiJ13I T ConsultantAuthor Commented:
Got it - cnt4 as (select count(*)/4 as c from tt) !

Thanks.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now