Ilmu Sql

Misalnya kita punya data:
123,345,678
234,567,789

dan kita ingin menampilkan data
123
345
678
234
567
789

Berikut script sederhana untuk men-split data.

select requester, b.Value approver, settingapproval_step from (
    select requestby_posid requester, approvedby_posid approver, settingapproval_step
    FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').anugerah_prod.dbo.vw_request_approval a
    where requestapproval_id=3 and requestby_posid=15351 and requestby_posid not in
    (
        select position_id FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').anugerah_prod.dbo.thrmposition where position_parentpath like '%11361%'
    ) and approvedby_posid not in( cast (14120 as varchar(100)) )--HR Officer   
) a
    cross apply
    (
        select  *  from  dbo.fn_split(a.approver, ',')
    ) b
    inner join (select * from OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').anugerah_prod.dbo.vw_employee_info) c on (b.Value=c.position_id)
order by settingapproval_step

USE [ssreport]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_Split]    Script Date: 08/29/2012 10:54:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[fn_Split](
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
returns @ValueTable table ([Value] nvarchar(4000))
begin
declare @NextString nvarchar(4000)
declare @Pos int
declare @NextPos int
declare @CommaCheck nvarchar(1)

--Initialize
set @NextString = ''
set @CommaCheck = right(@String,1)

--Check for trailing Comma, if not exists, INSERT
if (@CommaCheck <> @Delimiter )
set @String = @String + @Delimiter

--Get position of first Comma
set @Pos = charindex(@Delimiter,@String)
set @NextPos = 1

--Loop while there is still a comma in the String of levels
while (@pos <> 0)
begin
set @NextString = substring(@String,1,@Pos - 1)
insert into @ValueTable ( [Value]) Values (@NextString)
set @String = substring(@String,@pos +1,len(@String))
set @NextPos = @Pos
set @pos = charindex(@Delimiter,@String)
end

return
end

GO

select a.budgetid, budget, b.totalcost, amount,
case when c.budgetid is null then b.totalcost else b.totalcost+amount end x
from vbudgetcapex a
inner join (
    select budgetid, SUM(totalcost) totalcost from trcapexdetail    a
    inner join TrCapexHeader b on (a.capexid=b.capexid)
    where a.capexid=85
    group by budgetid
) b on (a.budgetid=b.budgetid)
left join [vcapex_pemakaianbudget] c on (a.budgetid=c.budgetid)
where budget < (case when c.budgetid is null then b.totalcost else b.totalcost+amount end)