Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2008
    Posts
    1

    Unanswered: Using column name as row value

    i all,
    I have a requirement and unable to solve this problem. I want ur urgent help. The problem is that there is a table like this

    Allowance1 Allowance2 Allowance3 empid

    12 13 14 56
    15 45 89 89
    78 102 89 20

    I want to change this as

    empid allowance amount
    56 allowance1 12
    56 allowance2 13
    56 allowance3 14
    89 allowance1 15
    89 allowance2 45
    89 allowance3 89


    Like this one I want as outcome


    Please help me in getting this output

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT empid
         , 'allowance1' As [allowance]
         , allowance1 As [amount]
    FROM   your_table
    
    UNION ALL
    
    SELECT empid
         , 'allowance2' As [allowance]
         , allowance2 As [amount]
    FROM   your_table
    
    UNION ALL
    
    SELECT empid
         , 'allowance3' As [allowance]
         , allowance3 As [amount]
    FROM   your_table
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    try this using unpivot

    select empid, allowance,amount
    from (select empid,allowance1,allowance2,allowance3 from allowance)p
    unpivot
    (amount for allowance in (allowance1,allowance2,allowance3))as unpvt

  4. #4
    Join Date
    Apr 2007
    Posts
    183
    I guess this answer wasn't enough...
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=116960

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •