Hi,
i would like to transpose a table (3 columns, 100 rows) to (30 columns
, 100 rows) i.e:

ID kw_name kw_value
1 NAME a1
1 NUM b1
2 NAME a2
2 NUM b2
3 NAME a3
3 NUM b3
....

TO:

ID NAME NUM
1 a1 b1
2 a2 b2
3 a3 b3
....

Basically the first table is in a warehouse, and the wanted result will
be a view from the first table.

I've tried :

select ID,
case kw_name when 'NAME' then kw_value end as NAME,
case kw_name when 'NUM' then kw_value end as NUM,
from table group by ID,kw_value,kw_name

but i have a wrong result:
ID NAME NUM
1 null null
1 a1 null
1 null b1
2 null null
2 a2 null
2 null b2
....


Do you have an idea ?

Hi David,

Try the following:

Create table t1 (ID int, kw_name char(03), kw_value char(02)) -- My
version of your table.

select ID,
"NAME"=max(substring(kw_value,1,3*(charindex('NAM' ,kw_name)))),

"NUM"=max(substring(kw_value,1,2*(charindex('NUM', kw_name))))
from t1
group by ID
go

ID NAME NUM
----------- ---- ---
1 a1 b1
2 a2 b2
3 a3 b3

(3 rows affected)

For variable length columns substitute 'datalength(kw_value)' for the
hard-coded '2' and '3' above ...

I think this table pivot is what you are looking for ...

Good Luck,

Sara ...

Dear Sara,

Thank you a lot ,
it works !

David.


DBAGAL wrote: