The only way to do what you are asking about is with dynamic SQL. If you wanted a pivot showing sales by department by year, you might not know how many departments or years there are. You would need a query to return all of your departments, and the iterate through that query to build sql strings for each line. Then you add each line to your query.
The real issue is how to handle the data once you build the query. If you are using something like .NET you can programatically output your data by looping through the columns or using AutoGenerateColumns = True. If you are using something like PL/SQL and Cursors, it is almost impossible.