Pivot a table in SQL without using pivot function

Question

Suppose there are two columns
Age Name
25   Nitin
30   Amit
27   Rishab
29   Ankush

Convert into
Name   Nitin.  Amit. Rishab. Ankush
Age.      25.       30.      27.        29

in progress 0
TheDataMonk 55 years 5 Answers 1546 views Grand Master 0

Answers ( 5 )

  1. Anuja.Halyal
    1
    Reply

    select ‘age’ as name
    , *
    from
    (
    select
    max(case when name like ‘Nitin’ then age end) as Nitin
    ,max(case when name like ‘Amit’ then age end) as Amit
    ,max(case when name like ‘Rishab’ then age end) as Rishab
    ,max(case when name like ‘Ankush’ then age end) as Ankush
    from
    );

  2. Anuja.Halyal
    0
    Reply

    select ‘age’ as name
    , *
    from
    (
    select
    max(case when name like ‘Nitin’ then age end) as Nitin
    ,max(case when name like ‘Amit’ then age end) as Amit
    ,max(case when name like ‘Rishab’ then age end) as Rishab
    ,max(case when name like ‘Ankush’ then age end) as Ankush
    from
    );

  3. Anuja.Halyal
    0
    Reply

    select ‘age’ as name
    , *
    from
    (
    select
    max(case when name like ‘Nitin’ then age end) as Nitin
    ,max(case when name like ‘Amit’ then age end) as Amit
    ,max(case when name like ‘Rishab’ then age end) as Rishab
    ,max(case when name like ‘Ankush’ then age end) as Ankush
    from
    );

  4. Harshit Goyal
    0
    Reply

    With CTE As
    (SELECT Age, Name, ROW_NUMBER() OVER() As Row_Number
    FROM table)
    SELECT
    MAX(CASE Name WHEN ‘Nitin’ Then Age End) Nitin,
    MAX(CASE Name WHEN ‘Amit’ Then Age End) Amit,
    MAX(CASE Name WHEN ‘Rishab’ Then Age End) Rishab,
    MAX(CASE Name WHEN ‘Ankush’ Then Age End) Ankush
    FROM CTE
    GROUP BY Row_Number

    0
    Reply

    Paste as Transpose.

Leave an answer

Browse
Browse