BAQ to display a concatenated field in separate rows. E.g. UserComp.PlantList

[michobe (Michael Cho)]

Hi

UserComp.PlantList displays user site lists in one field separated by ~.
Can BAQ show the site list in multiple rows? Thanks.

UserComp.PlantList
100~200~300

BAQ
100
200
300

Regards
Michael

[DVI_MMerrill (Melanie Merrill)]

Bump for same

[Yoonani (Yoonhwan Kim)]

Hi, I hope this code example help you.

with [CTE] as 
(
select 
	1 as [Calculated_n]	
from Ice.SysUserFile
where UserID = 'MANAGER'
union all
select 
	(CTE.Calculated_n + 1) as [Calculated_nn]
from  CTE  as CTE
where (CTE.Calculated_n + 1) < 100
)

select 
	UserFile.DcdUserID,
	(SUBSTRING(cast(UserFile.CompList as nvarchar(max)), CTE_TOP.Calculated_n, CHARINDEX('~', cast(UserFile.CompList as nvarchar(max)) + '~', CTE_TOP.Calculated_n) - CTE_TOP.Calculated_n)) as Company,
	(CTE_TOP.Calculated_n + 1 - LEN(REPLACE(LEFT(cast(UserFile.CompList as nvarchar(max)), CTE_TOP.Calculated_n), '~', '' ))) as [Calculated_index_id],	
	(SUBSTRING('~' + cast(UserFile.CompList as nvarchar(max)), CTE_TOP.Calculated_n, 1)) as [Calculated_where_1],
	(LEN(cast(UserFile.CompList as nvarchar(max))) + 1) as [Calculated_where_2],
	UserFile.CompList
from  (select 
	[CTE1].[Calculated_n] as [Calculated_n]
from  CTE  as CTE1)  as CTE_TOP
cross join Erp.UserFile UserFile
where  (SUBSTRING('~' + cast(UserFile.CompList as nvarchar(max)), CTE_TOP.Calculated_n, 1)) = '~'  and (LEN(cast(UserFile.CompList as nvarchar(max))) + 1) > CTE_TOP.Calculated_n  
and (cast(UserFile.CompList as nvarchar(max))) <> ''
--and DcdUserID = 'manager'
order by DcdUserID