How to get below query

Question

I have a table with one field name as City.

Below are the data.

City

--------
A
B
C
D
E
F
G
H

Output will be as Column as well

Write a query or stored procedure or function?

If a pass 2, the query result will be below

Col1  Col2
A      B
C      D
E      F
G      H

If a pass 3, the query result will be below

Col1 Col2 Col3
A     B     C
D     E     F
G     H

If a pass 4, the query result will be below

Col1 Col2 Col3 Clo4
A     B     C    D
E     F     G    H 

--Here script for creating and adding data into temp table
create table #Cities( City varchar(max) )

insert into #Cities values
( 'A' ),
( 'B' ),
( 'C' ),
( 'D' ),
( 'E' ),
( 'F' ),
( 'G' ),
( 'H' )


declare @what_I_pass as int = 2; -- Here pass the number you want.
 with Q1 as
(
    select *, ROW_NUMBER() over (order by City) - 1 as n
    from #Cities
),
Q2 as
(
    select City, n / @what_I_pass as rn, n % @what_I_pass as cn
    from Q1
)

select (stuff((select ' ' + City from Q2 as q where q.rn = Q2.rn order by cn for xml path('')), 1, 1, '')) as Result
from Q2
group by rn
order by rn

Answer 1

This would do:

declare @Cols int = 3  -- We define here how many output columns we want: 1, 2, 3, ... up to 10

create table #Cities( City varchar(max) )
insert into #Cities values
( 'A' ),
( 'B' ),
( 'C' ),
( 'D' ),
( 'E' ),
( 'F' ),
( 'G' ),
( 'H' )
;

with Cities as (
  select City,
         row_number() over (order by City) - 1 as Num
  from #Cities
)
select Col1.City as Col1, Col2.City as Col2, Col3.City as Col3, Col4.City as Col4, Col5.City as Col5, Col6.City as Col6, Col7.City as Col7, Col8.City as Col8, Col9.City as Col9, Col10.City as Col10
from Cities as Rows
     left join Cities as Col1  on 0 < @Cols and Col1.Num =  Rows.Num * @Cols + 0
     left join Cities as Col2  on 1 < @Cols and Col2.Num =  Rows.Num * @Cols + 1
     left join Cities as Col3  on 2 < @Cols and Col3.Num =  Rows.Num * @Cols + 2
     left join Cities as Col4  on 3 < @Cols and Col4.Num =  Rows.Num * @Cols + 3
     left join Cities as Col5  on 4 < @Cols and Col5.Num =  Rows.Num * @Cols + 4
     left join Cities as Col6  on 5 < @Cols and Col6.Num =  Rows.Num * @Cols + 5
     left join Cities as Col7  on 6 < @Cols and Col7.Num =  Rows.Num * @Cols + 6
     left join Cities as Col8  on 7 < @Cols and Col8.Num =  Rows.Num * @Cols + 7
     left join Cities as Col9  on 8 < @Cols and Col9.Num =  Rows.Num * @Cols + 8
     left join Cities as Col10 on 9 < @Cols and Col10.Num = Rows.Num * @Cols + 9
where Rows.Num <= (select max(Num) from Cities) / @Cols

drop table #Cities

Result for an input of 3 :

Col1    Col2    Col3    Col4    Col5    Col6    Col7    Col8    Col9    Col10
A       B       C       NULL    NULL    NULL    NULL    NULL    NULL    NULL
D       E       F       NULL    NULL    NULL    NULL    NULL    NULL    NULL
G       H       NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

PS: To avoid using dynamic SQL this query always returns a fixed number of 10 columns, but just the desired columns are filled. Now your presentation layer only needs to hide the unwanted empty columns.

Answer 2

Try this script,

declare @flg int=3--test with 1,2,3,4 ...
create table #temp(id int identity(1,1),col varchar(10))
insert into #temp values ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H')

create table #temp1(id int ,col varchar(10),flag int,rownum int)
declare @PvtCol varchar(500)=''
declare @Headcol varchar(500)
declare @Sql nvarchar(500)


WITH CTE
     AS (SELECT id,
                col,
                CASE
                    WHEN(id % @flg) = 0
                    THEN @flg
                    ELSE(id % @flg)
                END flag
         FROM #temp),
     CTE1
     AS (SELECT *,
                ROW_NUMBER() OVER(PARTITION BY flag
                ORDER BY id) rownum
         FROM cte)

     INSERT INTO #temp1
     (id,
      col,
      flag,
      rownum
     )
            SELECT id,
                   col,
                   flag,
                   rownum
            FROM cte1;

SELECT @PvtCol = COALESCE(@PvtCol + ', ' + QUOTENAME(rownum), QUOTENAME(rownum)),
       @Headcol = COALESCE(@Headcol + ', ' + QUOTENAME(rownum) + ' as ' + 'col', QUOTENAME(rownum) + ' as ' + 'col') + CAST(rownum AS VARCHAR)
FROM #temp1
WHERE flag = 1;

SET @PvtCol = STUFF(@PvtCol, 1, 1, '');

SELECT @PvtCol,
       @Headcol;

SET @Sql = 'select flag,' + @Headcol + ' from 
(
select flag, col,rownum from #temp1
)src
pivot(max(col) for rownum in(' + @PvtCol + ')) as pvt';

PRINT @Sql;

EXECUTE sp_executesql
        @Sql;

DROP TABLE #temp, #temp1;

Answer 3

Try below query:

--Here script for creating and adding data into temp table
declare @tbl table ( City varchar(max) )

insert into @tbl values
( 'A' ),
( 'B' ),
( 'C' ),
( 'D' ),
( 'E' ),
( 'F' ),
( 'G' ),
( 'H' )

declare @howManyCols int = 3;

;with cte as (
    select City, grp,
           row_number() over (partition by grp order by City) rn
    from (
        select *,
               (row_number() over (order by City) - 1) % @howManyCols + 1 grp
        from @tbl
    ) a
)
-- this query can be generated by dynamic SQL, because you can see pattern in following lft join's
select c1.City, c2.City, c3.City
from cte c1
left join cte c2 on c1.grp = c2.grp - 1 and c1.rn = c2.rn
left join cte c3 on c2.grp = c3.grp - 1 and c2.rn = c3.rn
where c1.grp = 1

For number of columns equal to three you get: