I have the following table in SQL Server:
| idx | value |
| --- | ----- |
| 1 | N |
| 2 | C |
| 3 | C |
| 4 | P |
| 5 | N |
| 6 | N |
| 7 | C |
| 8 | N |
| 9 | P |
I would like to turn it to this:
| idx 1-3 | idx 4-6 | idx 7-9 |
| ------- | ------- | ------- |
| N | P | C |
| C | N | N |
| C | N | P |
How can I do this?
You can compute the category of each row with a lateral join, then enumerate the rows within each category, and finally pivot with conditional aggregation:
select
max(case when cat = 'idx_1_3' then value end) as idx_1_3,
max(case when cat = 'idx_4_6' then value end) as idx_4_6,
max(case when cat = 'idx_7_9' then value end) as idx_7_9
from (
select t.*, row_number() over(partition by v.cat) as rn
from mytable t
cross apply (values (
case
when idx between 1 and 3 then 'idx_1_3'
when idx between 4 and 6 then 'idx_4_6'
when idx between 7 and 9 then 'idx_7_9'
end
)) v(cat)
) t
group by rn
You can use the mod as follows:
select max(case when idx between 1 and 3 then value end) as idx_1_3,
max(case when idx between 4 and 6 then value end) as idx_4_6,
max(case when idx between 7 and 9 then value end) as idx_7_9
from t
group by (idx-1) % 3;
If your idx is not continuous numbers then instead of from t use the following
from (select value, row_number() over(order by idx) as idx
from your_table t) t
Another solution with union all operator and row_number function
select max(IDX_1_3) as IDX_1_3, max(IDX_4_6) as IDX_4_6, max(IDX_1_3) as IDX_1_3
from (
select
case when idx in (1, 2, 3) then value end as idx_1_3
, null as idx_4_6
, null as idx_7_9
, row_number()over(order by idx) as rnb
from Your_table where idx in (1, 2, 3)
union all
select null as idx_1_3
, case when idx in (4, 5, 6) then value end as idx_4_6
, null as idx_7_9
, row_number()over(order by idx) as rnb
from Your_table where idx in (4, 5, 6)
union all
select null as idx_1_3
, null as idx_4_6
, case when idx in (7, 8, 9) then value end as idx_7_9
, row_number()over(order by idx) as rnb
from Your_table where idx in (7, 8, 9)
) t
group by rnb
;
drop table if exists #t;
create table #t (id int identity(1,1) primary key clustered, val varchar(20));
insert into #t(val)
select top (2002) concat(row_number() over(order by @@spid), ' - ', char(65 + abs(checksum(newid()))%26))
from sys.all_objects
order by row_number() over(order by @@spid);
select p.r, 1+(p.r-1)/3 grp3id, p.[1] as [idx 1-3], p.[2] as [idx 4-6], p.[3] as [idx 7-9]
from
(
select
val,
1+((1+(id-1)/3)-1)%3 as c3,
row_number() over(partition by 1+((1+(id-1)/3)-1)%3 order by id) as r
from #t
) as src
pivot
(
max(val) for c3 in ([1], [2], [3])
) as p
order by p.r;
If you want to split the data into three columns, with the data in order by id -- and assuming that the ids start at 1 and have no gaps -- then on your particular data, you can use:
select max(case when (idx - 1) / 3 = 0 then value end) as grp_1,
max(case when (idx - 1) / 3 = 1 then value end) as grp_2,
max(case when (idx - 1) / 3 = 2 then value end) as grp_3
from t
group by idx % 3
order by min(idx);
The above doesn't hard-code the ranges, but the "3" means different things in different contexts -- sometimes the number of columns, sometimes the number of rows in the result set.
However, the following generalizes so it adds additional rows as needed:
select max(case when (idx - 1) / num_rows = 0 then idx end) as grp_1,
max(case when (idx - 1) / num_rows = 1 then idx end) as grp_2,
max(case when (idx - 1) / num_rows = 2 then idx end) as grp_3
from (select t.*, convert(int, ceiling(count(*) over () / 3.0)) as num_rows
from t
) t
group by idx % num_rows
order by min(idx);
Here is a db<>fiddle.
