locked
How to display data at specific way using pivot or unpivot instead of cross apply RRS feed

  • Question

  • User1183902823 posted
    output
    1 Ajoy 2 Debdut 3 Somnath

    table has id and name column and i need to display data as per the above fashion.

    i use the below sql and it is working

    SELECT
        ca.ID, ca.[Name]
    FROM [Emp2]
    CROSS APPLY (
          Values
             ('ID' , cast(ID as varchar)),
             ('[Name]' , Name)
             
      ) as CA (ID, Name)

    just tell me how could i show the data as per my above fashion using pivot or unpivot instead of CROSS APPLY. help me with sample code.

    Monday, January 8, 2018 9:49 AM

Answers

  • User1183902823 posted

    i sort the issue this way too

    UNPIVOT way

    SELECT Result AS ID,Output AS Name
    FROM
    (SELECT CAST(ID AS VARCHAR(10)) AS ID,
    CAST(Name AS VARCHAR(10)) as Name
    FROM Emp2) stu
    UNPIVOT
    (Output FOR Result IN (ID, Name)
    ) AS mrks

    does with union

    SELECT [Output]
    FROM
    (
    SELECT CAST(id as varchar(20)) AS [Output],id as keyval,1 as sortord
    FROM tablename
    UNION ALL
    SELECT name, id, 2
    FROM tablename
    )t
    ORDER BY key

    thanks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 24, 2018 8:28 AM

All replies

  • User347430248 posted

    Hi tridip1974,

    I try to find a suitable example using unpivot but it looks like in newer version of SQL Cross Apply is the suitable approach to achieve your requirement.

    So i suggest you to continue using Cross Apply.

    Regards

    Deepak

    Saturday, January 20, 2018 6:18 AM
  • User1183902823 posted

    i sort the issue this way too

    UNPIVOT way

    SELECT Result AS ID,Output AS Name
    FROM
    (SELECT CAST(ID AS VARCHAR(10)) AS ID,
    CAST(Name AS VARCHAR(10)) as Name
    FROM Emp2) stu
    UNPIVOT
    (Output FOR Result IN (ID, Name)
    ) AS mrks

    does with union

    SELECT [Output]
    FROM
    (
    SELECT CAST(id as varchar(20)) AS [Output],id as keyval,1 as sortord
    FROM tablename
    UNION ALL
    SELECT name, id, 2
    FROM tablename
    )t
    ORDER BY key

    thanks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 24, 2018 8:28 AM