none
How to get below query RRS feed

  • Question

  • Dear All,

    I have a table with one field name as City.

    Below are the data.

    City

    --------

    A
    B
    C
    D
    E
    F
    G

    H

    Write a query or stored procedure or function?

    If a pass 2 , the query result will be below

    A      B

    C    D

    E    F

    G   H

    If a pass 3, the query result will be below

    A     B     C

    D    E      F

    G   H

    If a pass 4, the query result will be below

    A     B      C     D

    E     F     G     H

    If a pass 5, the query result will be and so on as above result.

    Thanks in advance

    Wednesday, October 9, 2019 5:25 PM

Answers

  • Try this adjustment:

    ; 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

    Thursday, October 10, 2019 4:57 AM

All replies

  • Formally this seems to work:

    drop table if exists #Cities
    
    create table #Cities( City varchar(max) )
    
    insert into #Cities values
    	( 'A' ),
    	( 'B' ),
    	( 'C' ),
    	( 'D' ),
    	( 'E' ),
    	( 'F' ),
    	( 'G' ),
    	( 'H' )
    
    
    declare @what_I_pass as int = 3
    
    
    ; 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 STRING_AGG(City, '   ') within group ( order by cn ) as Result
    from Q2
    group by rn
    order by rn
    
    /*
    
    Result
    A   B   C
    D   E   F
    G   H
    
    */
    


    Wednesday, October 9, 2019 7:31 PM
  • Hi SUNGG,

    Viorel has given a good solution. If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, October 10, 2019 1:59 AM
  • Hi Viorel,

    Thanks,

    How can be write this query in SQL server 2008?

    I think this problem solve by pivot operator as well.

    The string function STRING_AGG() is not there.

    Thanks

    Thursday, October 10, 2019 4:10 AM
  • Try this adjustment:

    ; 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

    Thursday, October 10, 2019 4:57 AM
  • Hi,

    When I run in SQL Server 2008, below are the output

    Result
    A
    B
    C
    D
    E
    F
    G
    H

    please help me.

    Thanks

    Thursday, October 10, 2019 5:22 AM
  • Hi Viorel,

    Thanks a lot.

    It's working now.

    Thursday, October 10, 2019 6:13 AM
  • Hi Viorel,

    Thanks a lot.

    It's working now.

    Hi SUNGG,

    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    Thanks for your contribution.

    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, October 10, 2019 6:37 AM
  • Hi,

    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

    Thursday, October 10, 2019 9:12 AM
  • Hi All,

    needs to create dynamic sql for column.Below are the script,please help me out to generate dynamic column as per pass the how many column as parameter.




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



    declare @howManyCols as int = 3; -- Here pass the number you want.


    ;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 #Cities
        ) 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
     --c4.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
    left join cte c4 on c3.grp = c4.grp - 1 and c3.rn = c4.rn
    where c1.grp = 1

    Thursday, October 10, 2019 12:20 PM
  • No, there is something wrong here. Maybe this is just a fun exercise you are doing, but permit me to say that in the general this does not make sense. A result set consists of a number of columns which each describes a separate attribute, and this is obviously not the case here.

    This is the get-started the forum, so I take it that you are new to SQL, and one of the things you need to learn is what you use SQL for and what you do not. This is not something you don't use SQL for. If this is a display issue, this is something you address in the presentation layer.

    Yes, we can write things like this in dynamic SQL, but if you are new to SQL, dynamic SQL is not for you. Dynamic SQL is advanced feature you an use once you know SQL well enough.
    In this case, the data should be return as a single column to the client who is then free to arrange the the data in the output as it sees fit.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, October 10, 2019 9:49 PM
    Moderator