UNPIVOT help

Transact-SQL
1

Hi,

I need help in creating the unPIVOT query for below requirement

OrderNo----Charge1----Charge2----Charge3----charge4------------------------around 20 columns--------------
12234-------100---------200-------------300------------100
34568--------200--------500-------------500------------100

select Order, chargedesc, amount
from table1
unpivot( amount For chargedesc IN (charge1,charge2,charge3,charge4)
) as t_unpivot

I am able to do UNPIVOT just with required column, but when I add with other 20 column and multiple joins it is giving error.

Any pointer will be helpful. thanks

2

We'll need the DDL (table definition) of all the tables in your query. And if you can provide some sample data, and the output you expect, that would be very helpful. We'd also need to see the query that's causing the error and the exact error message.

3

question is

what are you trying to do ? what does this mean ?
but when I add with other 20 column and multiple joins it is giving error.

Normally you pivot or unpivot on One Column ( i might be wrong .. )

4

A simple cross apply can be used to unpivot the charge amounts:

Declare @testData Table (OrderNo int, Charge1 money, Charge2 money, Charge3 money, Charge4 money, Charge5 money);
 Insert Into @testData (OrderNo, Charge1, Charge2, Charge3, Charge4, Charge5)
 Values (12234, 111, 211, 311, 411, 511)
      , (34568, 122, 222, 322, 422, 522)
      , (33333, 133, 233, 333, 433, 533);

 Select td.OrderNo
      , c.ChargeItem
      , c.ChargeAmount
   From @testData               td
  Cross Apply (Values (1, td.Charge1)
                    , (2, td.Charge2)
                    , (3, td.Charge3)
                    , (4, td.Charge4)
                    , (5, td.Charge5)) As c(ChargeItem, ChargeAmount);

You can add all of the charge amounts - as needed. If you need to add additional tables - it should then be simple to join to the other tables.

If there are errors - we cannot help without providing those errors.