Converting multiple rows to columns with specified

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

There are several ways that you can get the desired result. If you have a limited number of values that you want to PIVOT into columns, then you can hard-code the query a few different ways.

Aggregate function with CASE:

select 
  person,
  max(case when seq = 1 then category end) Cat1,
  max(case when seq = 1 then activity end) Cat1_Act,
  max(case when seq = 2 then category end) Cat2,
  max(case when seq = 2 then activity end) Cat2_Act,
  max(case when seq = 3 then category end) Cat3,
  max(case when seq = 3 then activity end) Cat3_Act
from
(
  select person, category, activity,
    row_number() over(partition by person
                      order by category) seq
  from yourtable
) d
group by person;

See SQL Fiddle with Demo. By assigning a sequence or row_number to each category per user, you can use this row number to convert the rows into columns.

Static PIVOT:

If you want to apply the PIVOT function, then I would first suggest unpivoting the category and activity columns into multiple rows and then apply the pivot function.

;with cte as
(
  select person, category, activity,
    row_number() over(partition by person
                      order by category) seq
  from yourtable
)
select person,
  cat1, cat1_act, 
  cat2, cat2_act,
  cat3, cat3_act
from
(
  select t.person, 
    col = case 
     when c.col = 'cat' then col+cast(seq as varchar(10))
      else 'cat'+cast(seq as varchar(10))+'_'+col
    end,
    value
  from cte t
  cross apply
 (
    select 'cat', category union all
    select 'act', activity
  ) c (col, value)
) d
pivot
(
  max(value)
  for col in (cat1, cat1_act, cat2, cat2_act,
              cat3, cat3_act)
) piv;

See SQL Fiddle with Demo

Dynamic PIVOT: Finally if you have an unknown number of values then you can use dynamic SQL to get the result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' 
                          + QUOTENAME(case 
                                       when d.col = 'cat' then col+cast(seq as varchar(10))
                                        else 'cat'+cast(seq as varchar(10))+'_'+col end) 
                    from 
                    (
                      select row_number() over(partition by person
                                                order by category) seq
                      from yourtable
                    ) t
                    cross apply
                    (
                      select 'cat', 1 union all
                      select 'act', 2
                    ) d (col, so)
                    group by col, so, seq
                    order by seq, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT person, ' + @cols + ' 
            from 
            (
              select t.person, 
                col = case 
                 when c.col = ''cat'' then col+cast(seq as varchar(10))
                  else ''cat''+cast(seq as varchar(10))+''_''+col
                end,
                value
              from 
              (
                select person, category, activity,
                  row_number() over(partition by person
                                    order by category) seq
                from yourtable
              ) t
              cross apply
             (
                select ''cat'', category union all
                select ''act'', activity
              ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo. All versions give a result:

|    PERSON | CAT1 | CAT1_ACT | CAT2 | CAT2_ACT | CAT3 | CAT3_ACT |
| Username1 |    X |       X1 |    Y |       Y1 |    Z |       Z1 |

Answer 2

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