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.
JimiJ13I T ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.