Split one column into multiple lines

Transact-SQL
1

Hi guys,

I have a table with data like this:

ID, Value
1 aaa:0,bbb:2,ccc:0,ddd:789...
2 aaa:1,bbb:0,ccc:0,ddd:478,eee:8...
3 aaa:0,bbb:6,ccc:14...

The output should be like this:

ID Value Time
1 aaa 0
1 bbb 2
1 ccc 0
1 ddd 789
2 aaa 1
2 bbb 0
2 ccc 0
2 ddd 478
2 eee 8

Any idea, please?

Thank you!

2

Here is the basic concept... (This will work as long as your first number is only 1 Character)

DECLARE @tags NVARCHAR(400) = '2 aaa:1,bbb:0,ccc:0,ddd:478,eee:8'

SELECT left(@tags,1) +' ' + value
FROM STRING_SPLIT(SUBSTRING (@tags,3,1000), ',')
WHERE RTRIM(value) <> '';

Simi

3

Try..

Declare @table as table
(
id int,
value varchar(256)
)

insert @table 
select '1','aaa:0,bbb:2,ccc:0,ddd:789' union all
select '2','aaa:1,bbb:0,ccc:0,ddd:478,eee:8' union all
select '3','aaa:0,bbb:6,ccc:14' ;

with cte
as
(

select  a.id , idx , MAX(a.value) [value]
from @table  a
cross apply
(
select top (len(',' + a.value + ',' ))  ROW_NUMBER() over( order by b.object_id asc) [rn]
from sys.objects b
) b
cross apply
(
select CHARINDEX(',', ',' + a.value + ',' , b.rn ) idx 
)c
group by id, idx 
)

select a.id ,  
Left(b.x,  CHARINDEX(':',   b.x ) - 1) [value]
, Right(b.x,  LEN(b.x) -  CHARINDEX(':',   b.x )) [Time]

from cte  a
outer apply (  select    SUBSTRING( a.value, a.idx ,  (select top (1) b.idx  from cte b where b.id = a.id and b.idx > a.idx) - a.idx - 1  ) x        )  b
where b.x is not null 
order by a.id;