thanks
dwain.c
it was working fine
can u plz give one idea is there any chance to avoid null value instead of that replace 0 there in column in this proc itself
declare @parameter varchar (200)
set @parameter ='1_2_3|4_5'
SELECT ss=MAX(CASE c.itemnumber WHEN 1 THEN c.item END)
,col=MAX(CASE c.itemnumber WHEN 2 THEN c.item END)
,col1=MAX(CASE c.itemnumber WHEN 3 THEN c.item END)
FROM (SELECT @parameter) a(parameter)
CROSS APPLY dbo.DelimitedSplit8k(parameter, '|') b
CROSS APPLY dbo.DelimitedSplit8k(item, '_') c
GROUP BY b.ItemNumber
i am getting output like this
sscolcol1
123
45NULL
and trying output like this
sscolcol1
123
450
i tried like this
declare @parameter varchar (200)
set @parameter ='1_2_3|4_5'
SELECT ss=MAX(CASE c.itemnumber WHEN 1 THEN c.item END)
,col=MAX(CASE c.itemnumber WHEN 2 THEN c.item END)
,col1=MAX(CASE c.itemnumber WHEN 3 THEN ISNULL(c.item,0) END)
FROM (SELECT @parameter) a(parameter)
CROSS APPLY dbo.DelimitedSplit8k(parameter, '|') b
CROSS APPLY dbo.DelimitedSplit8k(item, '_') c
GROUP BY b.ItemNumber