1. INTRODUCTION

Some days ago,  I had to help others to solve a problem with SQL. This issue was related to convert fields in a result set into values in rows into the final desired result set. So, we are talking about making use of UNPIVOTING functionality. Finally, I thought, why not writing a little tip about?

2. BACKGROUND

Here it is the original result set:

And here it is the desired final result set:

3. USING THE CODE

Let's get straight to the point to solve our problem.

To begin with, we will create a table variable and populate it with data:

declare @MyTable table(
    [Total Overdue] money, 
    [Total Charged] money,
    [Total Interest] money,
    [Total Penalty] money
    )
   
insert into @MyTable
values(1000.00,55.01,20.14,5747.00)

If you execute the previous query, you will get the original result set shown above.

Well, at this point,  we will code another query to obtain our desired result:

select [Total Type], [Total Money] from
   (select * from @MyTable) OriginalQuery
UNPIVOT([Total Money] for [Total Type] 
   in ([Total Overdue],[Total Charged],[Total Interest],[Total Penalty])) FinalQuery

First, you should notice use of UNPIVOT operator. In addition to this, pay attention to the new field names given to the new result set by means of the previous operator ([Total Type] and [Total Money]). I have employed these, but you might set up others. Only, take care to use same names within UNPIVOT clause and final SELECT.

4. POINTS OF INTEREST

I think that strong knowledge of advanced operators, such us UNPIVOT, is always very good for any SQL developer. You will gain skills and time.

As always, I hope this is useful for anyone.