Search code examples
sqlsql-server-2008unpivot

How do i can convert Column Values Into Row?

Here I'm having table 'TABLE_1' as

|ID  | Name | ACID1 | ACVALUE1 | ACID2  | ACVALUE2 | ACID3 | ACVALUE3 |
|----------------------------------------------------------------------
| 1  |ABC   |  10   |  82.50   |   20   | 175.95   |  40   |  125.75  |           
| 2  |IJK   |  30   | 120.55   |   20   |  68.30   |  50   |   25.45  |
| 3  |LMN   |  40   |  62.50   |   10   |  87.25   |  30   |   40.50  |
 ----------------------------------------------------------------------

Another table is AC_TABLE whose ID is recored in above table as ACID1,ACID2,...

___________________
|ID    |   Name    | 
|-------------------
|10    |   AC1     |
|20    |   AC2     |
|30    |   AC3     |
|40    |   AC4     |
|50    |   AC5     |
-------------------

Now what all i want the result in following format

_____________________________
ID | Name |  ACName |  ACVALUE
------------------------------
1  | ABC  |   AC1   |  82.50
1  | ABC  |   AC2   | 175.95
1  | ABC  |   AC4   | 125.75
2  | IJK  |   AC3   | 120.55
2  | IJK  |   AC2   |  68.30
2  | IJK  |   AC5   |  25.45
3  | LMN  |   AC4   |  62.50
3  | LMN  |   AC1   |  87.25
3  | LMN  |   AC3   |  40.50
-------------------------------

Please help me to get the desired result.

Solution

  • Use Cross Apply to unpivot multiple columns.

    First unpivot the Table_1 using cross apply then join the result with the ac_table table. Try this.

    SELECT a.id,
       a.name,
       b.name AS ACName,
       cs.ACValue
FROM   Table_1 a
       CROSS apply (VALUES (ACVALUE1,ACID1),
                           (ACVALUE2,ACID2),
                           (ACVALUE3,ACID3))cs(acvalue, ac)
       JOIN ac_table b
         ON cs.ac = b.id

    SQLFIDDLE DEMO