I don't think you can do this using PIVOT operator. And definitely, you will need dynamic SQL for that. I'm not even sure that you should do this with T-SQL. Maybe the application is better place to construct this table. Anyway, you have been warned!
My idea is to construct query like this:
;with
cte001 as (select row_number() over(order by col1) as RowNo, col2 from #t where col1 = '001'),
cte002 as (select row_number() over(order by col1) as RowNo, col2 from #t where col1 = '002'),
cte004 as (select row_number() over(order by col1) as RowNo, col2 from #t where col1 = '004'),
cte006 as (select row_number() over(order by col1) as RowNo, col2 from #t where col1 = '006'),
cte007 as (select row_number() over(order by col1) as RowNo, col2 from #t where col1 = '007')
select cte001.col2 as [001], cte002.col2 as [002], cte004.col2 as [004], cte006.col2 as [006], cte007.col2 as [007]
from (select row_number() over(order by col1) as RowNo from #t) AllRowNums
left join cte001 on cte001.RowNo = AllRowNums.RowNo
left join cte002 on cte002.RowNo = AllRowNums.RowNo
left join cte004 on cte004.RowNo = AllRowNums.RowNo
left join cte006 on cte006.RowNo = AllRowNums.RowNo
left join cte007 on cte007.RowNo = AllRowNums.RowNo
where coalesce( cte001.col2, cte002.col2, cte004.col2, cte006.col2, cte007.col2) is not null
It will return as many columns, as distinct codes you have in your table, and in each column it will return in first rows the relevant values for this code. To do this, I will calculate the row number per code and join on it. It is possible to calculate this row number in single CTE using partition by col1 in the over clause and join the same cte multiple times, but the query above seems to be more clear.
And here is the whole code to construct a query like the one above:
drop table if exists #t;
create table #t(col1 varchar(10), col2 varchar(50));
insert into #t values
('001', '001-TIPOLOGIA'),
('001', '001-MATERIALE'),
('002', '002-TIPOLOGIA'),
('002', '002-MATERIALE'),
('002', '002-DIAMETRO_ESTERNO'),
('002', '002-LUNGHEZZA_FILETTATURA'),
('004', '004-TIPOLOGIA'),
('004', '004-DIAMETRO'),
('006', '006-TIPOLOGIA'),
('006', '006-MATERIALE'),
('006', '006-QUALITA'),
('006', '006-DIAMETRO_EXT'),
('006', '006-DIAMETRO_INT'),
('006', '006-SPESSORE'),
('006', '006-NORME_RIFERIMENTO'),
('006', '006-PEZZI_CONFEZIONE'),
('007', '007-TIPO_FILTRO'),
('007', '007-DIMENSIONE_FILTRO')
-- Construct the actual query
declare @sql nvarchar(max) = ';with';
select @sql += CONCAT('
cte', col1, ' as (select row_number() over(order by col1) as RowNo, col2 from #t where col1 = ''', col1, '''),') from (select distinct col1 from #t) t
set @sql = SUBSTRING(@sql, 1, len(@sql) - 1); -- remove the last comma
set @sql += '
select';
select @sql += CONCAT(' cte', col1, '.col2 as [', col1, '],') from (select distinct col1 from #t) t
set @sql = SUBSTRING(@sql, 1, len(@sql) - 1); -- remove the last comma
set @sql += '
from (select row_number() over(order by col1) as RowNo from #t) AllRowNums';
select @sql += CONCAT('
left join cte', col1, ' on cte', col1, '.RowNo = AllRowNums.RowNo') from (select distinct col1 from #t) t
set @sql += '
where coalesce(';
select @sql += CONCAT(' cte', col1, '.col2,') from (select distinct col1 from #t) t
set @sql = SUBSTRING(@sql, 1, len(@sql) - 1); -- remove the last comma
set @sql += ') is not null'
--print @sql
exec sp_executesql @sql;
