Converting multiple rows to columns with specified heading

Question

I have a table that holds details of activities carried out by individuals - contents of this table is similar to the following:

| Person    | Category  | Activity   |
--------------------------------------
| Username1 | X         | X1         |
| Username1 | Y         | Y1         |
| Username1 | Z         | Z1         |

I need a SQL query that can produce something like the following and any help would be appreciated:

| Person    | Cat1 | Cat1_Act|Cat2 | Cat2_Act| Cat3  | Cat3_Act |
---------------------------------------------------------------
| Username1 | X    | X1      | Y   | Y1      | Z     |    Z1    |

I understand reading through a number of posts that PIVOT can be used to achieve this but I have not been to find a solution close to what I need as most solutions are often to use values e.g 'X', 'Y', 'Z' (in my example table) as table headers but I want to ideally be able to specify name for the table headers holding the new columns (Hope this all makes sense and someone can help :-) )

Answer 1

this is a simple example

 SELECT 
       Person,  
       MAX(CASE Category WHEN 'X' THEN Activity ELSE 0 END) AS 'X'
       MAX(CASE Category WHEN 'Y' THEN Activity ELSE 0 END) AS 'Y'
       MAX(CASE Category WHEN 'Z' THEN Activity ELSE 0 END) AS 'Z' 
    FROM mytable
    GROUP BY Person