group by first column, and split (pivot?) remaining two columns

Question

TSQL question here. See source and desired output in image below. Code to build source table is also provided.

---

DECLARE @tablevar TABLE(
record nvarchar(10),
category nvarchar(50),
value float)

INSERT INTO @tablevar
VALUES
('110-AL','credits_cle',1),
('110-AL','credits_ethics',2),
('110-AR','credits_ethics',2.5),
('110-AZ','credits_prof_resp',1.5),
('110-AZ', 'credits_ethics',5),
('110-AZ', 'credits_cle',4)

Answer 1

Since you want to PIVOT two columns of data, one way you can do this would be to apply both the UNPIVOT and the PIVOT functions. The UNPIVOT will convert the multiple columns category and value to multiple rows, then you can apply the PIVOT to get the final result:

select record, 
  category1, value1, 
  category2, value2, 
  category3, value3
from
(
  select record, col+cast(seq as varchar(10)) col, val
  from
  (
    select record, category, 
      cast(value as nvarchar(50)) value,
      row_number() over(partition by record order by category) seq
    from tablevar
  ) d
  unpivot
  (
    val
    for col in (category, value)
  ) unpiv
) src
pivot
(
  max(val)
  for col in (category1, value1, category2, value2, category3, value3)
) piv;

See SQL Fiddle with Demo.

If you have an unknown number of values, then you will have to use dynamic SQL similar to this:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(seq as varchar(10))) 
                    from
                    (
                      select row_number() over(partition by record order by category) seq
                      from tablevar
                    ) d
                    cross apply
                    (
                      select 'category', 1 union all
                      select 'value', 2
                    ) c (col, so)
                    group by seq, so, col
                    order by seq, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT record,' + @cols + ' 
             from 
             (
               select record, col+cast(seq as varchar(10)) col, val
                from
                (
                  select record, category, 
                    cast(value as nvarchar(50)) value,
                    row_number() over(partition by record order by category) seq
                  from tablevar
                ) d
                unpivot
                (
                  val
                  for col in (category, value)
                ) unpiv
            ) x
            pivot 
            (
                max(val)
                for col in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo