How to split a columns value to different columns T-sql-CodePudding

I have:

data      Subaru,Power   Toyota,Power 
--------- ------------   ----------
01.01.03     2333            7777

I need:

data       Car type   Power
-----      --------  --------
01.01.03   Subaru      2333
01.01.03   Toyota      7777

The idea was by using T-sql via loop (The column name is equalt to %%(%)) declare @TABLE_NAME nvarchar(100) = N'something_in_the_way_Meiram' declare @column_name nvarchar(max) declare @sql nvarchar(max) declare db_cursor cursor for select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TABLE_NAME and COLUMN_NAME like N'%%(%)' open db_cursor FETCH next from db_cursor into @column_name while @@fetch_status = 0 begin declare @job nvarchar(100) select top(1) @job=value from string_split(@column_name,'') if(@job like N'Добыча руды') begin set @sql = N' select top(1) @meh = value from string_split(N''' @column_name ''','''')' end

end

CodePudding user response：

you could use a query like below

select 
data,
CarType,
Power
from yourtable cross apply 
(values ('Subaru',[SubaruPower]),('Toyota',[ToyotaPower])
)v(CarType,Power)

CodePudding user response：

Hello this solution is in oracle

       SELECT * FROM yourtable
       UNPIVOT(
       "POWER"   -- unpivot_clause
       FOR CAR_TYPE --  unpivot_for_clause
       IN ( -- unpivot_in_clause
       SUBARU_POWER  AS 'SUBARU', 
       TOYOTA_POWER  AS 'TOYOTA'
)

) ;