SQL Transformation from rows to columns

Question

I have a table

ID VALUE BRAND
1 group1 Hollister
1 group2 Express
1 group 3 Persche 
2 Group1 Hollister
3 Group3 Persche

and so on. and want to transform to something like this which are unique on ID in sql:

ID Hollister Express Persche 
1  Group1   Group2   Group3 
2  Group1   BLANK    Blank 
3  Blank    Blank    Group3

Answer 1

You can do this with a pivot or conditional aggregation:

select id,
       max(case when brand = 'Hollister' then value end) as Hollister,
       max(case when brand = 'Express' then value end) as Express,
       max(case when brand = 'Persche' then value end) as Persche
from table t
group by id;

This will produce NULL. If you actually want '':

select id,
       max(case when brand = 'Hollister' then value else '' end) as Hollister,
       max(case when brand = 'Express' then value else '' end) as Express,
       max(case when brand = 'Persche' then value else '' end) as Persche
from table t
group by id;

Answer 2

Here is a working PIVOT option:

Select  * 
From 
(
    Select  value, Brand, id
    From    YourTable
) Source
Pivot
(
    Max(value)
    For Brand In ([Hollister],[Express],[Persche])
) P

Answer 3

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table.

here's an example :Convert Rows to columns using 'Pivot' in SQL Server

select *
from 
(
  select store, week, xCount
  from yt
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;