locked
How to Convert Rows to Column in Sql Server RRS feed

  • Question

  • User1194139608 posted

    Hey ,

    I have a table with name of tbl_product (tbl_id, product_name) regurarly data insertion

    like:

    tbl_id , product_name

    1          Samsung

    2         Iphone

    3        Nokia

    4        Sony

    5         Other

    6       micromax

     

    can we create query like this can data represet like this

    Column1, Column2 ,column3 , COlumn4

    1                2                3              4

    5                6                _               _

     

    means all tbl_id store in temp table and show like that

    Friday, February 5, 2016 5:57 AM

Answers

  • User-62323503 posted
    declare @tab table(tbl_id int, product_name varchar(50))
    insert into @tab values
    (1,'Samsung'),(2,'Iphone'),(3,'Nokia'),(4, 'Sony'),(5,'Other'),(6,'micromax')
    
    select
        [0] as Column1,
        [1] as Column2,
        [2] as Column3,
        [3] as Column4
    from
    (
        SELECT 
            tbl_id,
            (ROW_NUMBER() OVER(ORDER BY tbl_id) - 1) / 4 as GroupNum,
            (ROW_NUMBER() OVER(ORDER BY tbl_id) - 1) % 4 as ColNum
        FROM @tab
    ) as tr
    pivot
    (
        max(tbl_id) for ColNum in ([0], [1], [2], [3])
    ) as p

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, February 7, 2016 9:26 AM

All replies

  • User632428103 posted

    Hello twinkle 1989,

    you need to use pivot function :

    http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server

    hope this help

    Friday, February 5, 2016 8:00 AM
  • User941753370 posted

    Hi,

    Try:

    select
        [0] as Column1,
        [1] as Column2,
        [2] as Column3,
        [3] as Column4
    from
    (
        SELECT 
            tbl_id,
            NTILE(4) OVER(ORDER BY tbl_id) as GroupNum,
            (ROW_NUMBER() OVER(ORDER BY tbl_id) - 1) % 4 as ColNum
        FROM tbl_product
    ) as t
    pivot
    (
        max(tbl_id) for ColNum in ([0], [1], [2], [3])
    ) as p

    Hope this help

    Friday, February 5, 2016 12:13 PM
  • User-62323503 posted
    declare @tab table(tbl_id int, product_name varchar(50))
    insert into @tab values
    (1,'Samsung'),(2,'Iphone'),(3,'Nokia'),(4, 'Sony'),(5,'Other'),(6,'micromax')
    
    select
        [0] as Column1,
        [1] as Column2,
        [2] as Column3,
        [3] as Column4
    from
    (
        SELECT 
            tbl_id,
            (ROW_NUMBER() OVER(ORDER BY tbl_id) - 1) / 4 as GroupNum,
            (ROW_NUMBER() OVER(ORDER BY tbl_id) - 1) % 4 as ColNum
        FROM @tab
    ) as tr
    pivot
    (
        max(tbl_id) for ColNum in ([0], [1], [2], [3])
    ) as p

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, February 7, 2016 9:26 AM