sql server - SQL Query returns comma separated data -


i running below query through ms query on odbc connection:

select oa_cstexpbal_0.costcentre, oa_cstexpbal_0.expensecode, oa_cstexpbal_0.yearno, oa_cstexpbal_0.baltype, oa_cstexpbal_0.openbal, oa_cstexpbal_0.periodbal openacc.pub.oa_cstexpbal oa_cstexpbal_0 (oa_cstexpbal_0.yearno='2016') , (oa_cstexpbal_0.baltype='av') 

and returns below data 1 column periodbal:

406186.06;317084.39;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0 

is there way can separate out periodbal data stop coming through comma separated?

i connecting sql 2008 db call openacc.pub , using original query pull through data. column retuning ; periodbal. when dumped excel can corrected through text columns ideally want try , in query (if possible).

assuming use ms sql server want split data separated semicolon in column rows handle them in sql.

if correct, need split them. sql server can not default have various way so.

see post http://sqlperformance.com/2012/07/t-sql-queries/split-strings general informatione.

here https://codereview.stackexchange.com/questions/15125/sql-server-split-function-optimized posted once split function, works in sql, too.

so 1 approach can be:

select oa_cstexpbal_0.costcentre   , oa_cstexpbal_0.expensecode   , oa_cstexpbal_0.yearno   , oa_cstexpbal_0.baltype   , oa_cstexpbal_0.openbal   , oa_cstexpbal_0.periodbal   , s.value periodbal_as_rows openacc.pub.oa_cstexpbal oa_cstexpbal_0 cross apply dbo.fn_split(oa_cstexpbal_0.periodbal,';') s (oa_cstexpbal_0.yearno='2016') , (oa_cstexpbal_0.baltype='av') 

Comments

Popular posts from this blog

matlab - "Contour not rendered for non-finite ZData" -

delphi - Indy UDP Read Contents of Adata -

javascript - Any ideas when Firefox is likely to implement lengthAdjust and textLength? -