SQL Query returns comma separated data

Question

I am running the below query through MS Query on an 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
FROM OPENACC.PUB.oa_cstexpbal oa_cstexpbal_0
WHERE (oa_cstexpbal_0.yearno='2016') AND (oa_cstexpbal_0.baltype='AV')

and it returns the below data into one column for 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 a way I can separate out the periodbal data to stop it coming through as comma separated?

I am connecting to a sql 2008 Db call OPENACC.PUB and using the original query to pull through the data. The only column that is retuning with a ; is the periodbal. when dumped into excel this can be corrected through Text To Columns but ideally I want to try and do this in the query (if possible).

Answer 1

Assuming you use MS SQL Server you want to split data which are separated by semicolon in your column to rows so you handle them in SQL.

If that is correct, then you need to split them. As SQL server can not do this by default you have various way to do so.

See this post http://sqlperformance.com/2012/07/t-sql-queries/split-strings for some general informatione.

Here https://codereview.stackexchange.com/questions/15125/sql-server-split-function-optimized I posted once a split function, that works in SQL, too.

So one 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 AS periodbal_as_rows
FROM OPENACC.PUB.oa_cstexpbal oa_cstexpbal_0
CROSS APPLY dbo.fn_Split(oa_cstexpbal_0.periodbal,';') AS s
WHERE (oa_cstexpbal_0.yearno='2016') AND (oa_cstexpbal_0.baltype='AV')