0

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).

PIPRON79
  • 131
  • 1
  • 1
  • 11
  • 1
    That must be how the column is in the database, MySQL doesn't add commas if they weren't there. – Barmar Nov 27 '14 at 10:26
  • 2
    BTW, those aren't commas, they're semicolons. comma = `,`, semicolon = `;`. – Barmar Nov 27 '14 at 10:27
  • 1
    Why are we talking about MySQL anyway? – Strawberry Nov 27 '14 at 10:42
  • thanks for your comments... is there anyway to split the data from ; that i could code into the sql? – PIPRON79 Nov 27 '14 at 10:58
  • Can you show more code? Verify what database you are using and the code you have to connect and pull the data back. To answer your last comment, yes there are ways to split the data but how that is done depends heavily on the dbms and how you are retrieving the data. – Fred Nov 27 '14 at 11:14
  • re-tagged with `sql-server` based on "*connecting to a sql 2008*" assuming that "SQL 2008" means "SQL **Server** 2008" –  Nov 27 '14 at 13:26

1 Answers1

0

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')
Community
  • 1
  • 1
YvesR
  • 5,922
  • 6
  • 43
  • 70
  • thanks for that YvesR. Is it possible to do this through MS query via excel? – PIPRON79 Nov 27 '14 at 14:18
  • Well if you register the function in your SQL database you can query this in Excel as well. If the ODBC provider make any problems, setup a query (CREATE QUERY ...) and use this one. – YvesR Nov 27 '14 at 15:09