none
Multi Rows and output in Multi Columns

    Question

  • Hi,

    Looking for help to write query for this unique requriement.

    My Table has data like the following, two columns but many rows

    ItemId   ItemName

    1 A

    2 B

    3 C

    4 D

    5 E

    I want query should give me result as:-

    ItemId  ItemName    ItemId   ItemName    ItemId   ItemName

    1 A   3 C     5  E

    2 B  4 D

    Could you please help me or give me some suggestions, how to write this query.

    Thanks in advance.

    Wednesday, April 03, 2013 8:26 PM

Answers

  • This is basically a job for the client software to do this kind of formatting. But if you insist:

    ;with cte as (select Item_Id, Item, ROW_NUMBER() over (order BY Item_Id) as Rn
    from dbo.items)
    
    SELECT 
    max(case when Rn%3=1 then Item_id end) as ItemId1,
    max(case when Rn%3=1 then Item else '' end) as ItemName1,
    max(case when Rn%3=2 then Item_id end) as ItemId2,
    max(case when Rn%3=2 then Item else '' end) as ItemName2,
    max(case when Rn%3=0 then Item_id end) as ItemId3,
    max(case when Rn%3=0 then Item else '' end) as ItemName3
    from cte 
    GROUP BY (Rn-1)/3 
    ORDER BY ItemId1
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by AJamil Thursday, April 04, 2013 1:05 AM
    Wednesday, April 03, 2013 9:03 PM
    Moderator

All replies

  • please read any book on RDBMS and pay attention to First Normal Form. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, April 03, 2013 8:53 PM
  • This is basically a job for the client software to do this kind of formatting. But if you insist:

    ;with cte as (select Item_Id, Item, ROW_NUMBER() over (order BY Item_Id) as Rn
    from dbo.items)
    
    SELECT 
    max(case when Rn%3=1 then Item_id end) as ItemId1,
    max(case when Rn%3=1 then Item else '' end) as ItemName1,
    max(case when Rn%3=2 then Item_id end) as ItemId2,
    max(case when Rn%3=2 then Item else '' end) as ItemName2,
    max(case when Rn%3=0 then Item_id end) as ItemId3,
    max(case when Rn%3=0 then Item else '' end) as ItemName3
    from cte 
    GROUP BY (Rn-1)/3 
    ORDER BY ItemId1
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by AJamil Thursday, April 04, 2013 1:05 AM
    Wednesday, April 03, 2013 9:03 PM
    Moderator
  • Great, that exactly I was looking for.

    Thanks

    Thursday, April 04, 2013 1:06 AM