sivajii (8/27/2012)

---

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