Convert rows to columns in SQL Server

You can use the PIVOT function to get the result. Since you are going to have an unknown number of SKU values, I would first write a hard-coded version of the query then convert it to dynamic SQL.

Since you have two columns that you want to pivot data from value and volume, then I would first unpivot those two columns into multiple rows and then apply the PIVOT function. The unpivot syntax can use the UNPIVOT function or CROSS APPLY:

select col = t.sku+'_'+ c.col, 
  c.val
from yourtable t
cross apply
(
  select 'value', value union all
  select 'volume', volume
) c (col, val)

See Demo. This gives a result:

|       COL | VAL |
|  AB_value |   2 |
| AB_volume |   3 |
|  AB_value |   2 |
| AB_volume |   2 |
|  BB_value |   1 |
| BB_volume |   3 |

Once you have the data into a format that is similar to this, then you can apply the PIVOT:

select AB_Value, BB_Value, AB_Volume, BB_Volume
from
(
  select col = t.sku+'_'+ c.col, 
    c.val
  from yourtable t
  cross apply
  (
    select 'value', value union all
    select 'volume', volume
  ) c (col, val)
) d
pivot
(
  sum(val)
  for col in (AB_Value, BB_Value, AB_Volume, BB_Volume)
) piv;

See SQL Fiddle with Demo. Now since you will have an unknown number of values, then you will have to use dynamic SQL to generate the SQL string that you need to execute:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(sku+'_'+col) 
                    from yourtable
                    cross apply
                    (
                      select 'value', 1 union all
                      select 'volume', 2
                    ) c (col, so)
                    group by sku, so, col
                    order by so, sku
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT ' + @cols + ' 
            from 
            (
              select col = t.sku+''_''+ c.col, 
                c.val
              from yourtable t
              cross apply
              (
                select ''value'', value union all
                select ''volume'', volume
              ) c (col, val)
            ) x
            pivot 
            (
                sum(val)
                for col in (' + @cols + ')
            ) p '

execute sp_executesql @query;

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

| AB_VALUE | BB_VALUE | AB_VOLUME | BB_VOLUME |
|        4 |        1 |         5 |         3 |

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Table1
    ([SKU] varchar(2), [Value] int, [Volume] int)
;

INSERT INTO Table1
    ([SKU], [Value], [Volume])
VALUES
    ('AB', 2, 3),
    ('AB', 2, 2),
    ('BB', 1, 3)
;

Query 1:

DECLARE @colTable table(rownum int,columnName varchar(20),value int,volume int)
INSERT INTO @colTable SELECT row_number() over (ORDER BY SKU), SKU, 
                             SUM(Value), SUM(Volume) FROM Table1 GROUP BY SKU
DECLARE @query varchar(500)
DECLARE @i int, @max int
SET @i = 1
SET @max = (SELECT count(*) FROM @colTable)
SET @query = 'select '
WHILE @i <= @max
BEGIN
    IF @i > 1 SET @query = @query + ','
    SET @query = @query + 
             (SELECT cast(value as varchar) FROM @colTable WHERE rownum = @i) + 
             ' AS "' + 
             (SELECT columnName FROM @colTable WHERE rownum = @i) + '(Value)"'
    SET @query = @query + ','
    SET @query = @query + 
             (SELECT cast(volume as varchar) FROM @colTable WHERE rownum = @i) + 
             ' AS "' + 
             (SELECT columnName FROM @colTable WHERE rownum = @i) + '(Volume)"'
    SET @i = @i + 1
END
exec(@query)

Results:

| AB(VALUE) | AB(VOLUME) | BB(VALUE) | BB(VOLUME) |
|-----------|------------|-----------|------------|
|         4 |          5 |         1 |          3 |