Forum Discussion
Unpivot data into multiple columns
Hello,
I have a data set with the following structure (columns)
Attribute SalesYear1 SalesYear1 SalesYear1 CostYear1 CostYear2 CostYear3
I would like to transform this into the following structure (columns)
Attribute Year Sales Costs
I think it would be staright forward without the cost column. However, I don't know how to unpiviot into more than two rows.
Thank you in advance for any help
Duplicate the query.
In one, unpivot the Sales columns and remove the cost columns.
In the other, unpivot the Costs and remove the sales columns.
In each query, alter the data so you have Year in a column by itself
'Merge as New' the two queries on Year and Division (Inner Join).
Expand and tidy as appropriate.
4 Replies
Can you provide some sample data please
Duplicate the query.
In one, unpivot the Sales columns and remove the cost columns.
In the other, unpivot the Costs and remove the sales columns.
In each query, alter the data so you have Year in a column by itself
'Merge as New' the two queries on Year and Division (Inner Join).
Expand and tidy as appropriate.