T-SQL using how to use PIVOT function

Question

I have the following table structure in SQL (using T-SQL): sqlfiddle: http://sqlfiddle.com/#!6/e5edc/1/0

The data would look something like this:

Now I would like to transpose the structure so I get the following:

• columns [01_amount] to [12_amount] and columns [01_active] to [12_active] as rows instead of columns
• All rows of [initials] to be separate columns

Should look like this:

How would I go about this? The Pivot function looks rather complicated as I'm new to SQL. Can someone help me in the right direction? :-)

Answer 1

Ok you will need first to unpivot your data, which is done in cte. Then you will need to pivot again:

;with cte as(select initials, v, col from main
unpivot(v for col in([01_amount], [02_amount])) u)
select * from cte
pivot(max(v) for initials in([rw],[nb]))p

In unpivot part just add all 24 column names for amounts and active bits. In pivot part just add all possible values for initials.

But if you don't want to manually list all possible values for initials then you will need to make some dynamic query with unpivoting and pivoting.

Here is demo for 2 columns and you will easily expand it http://sqlfiddle.com/#!6/4cf36/2