Re: Break up a field into multiple rows

I assume that you don't use the query builder for this. There are so many constructs that the query builder doesn't support...

Most probable reason is that the column where you have the LEFT and RIGHT expressions aren't given a name. And tables/views can't have columns with no name. Try:

,LEFT(territories, CHARINDEX(' ', territories)) AS yourcolname ,RIGHT(territories, CHARINDEX(' ', territories)) AS yourcolname

(In SQL Server, the column name in a UNION is picked from the first query. In ANSI SQL it is, as I recall it, undefined in case of conflict; which is why I prefer to be consistent across the SELECTs in the UNION.)

--
Tibor Karaszi


"Todd Benson" <toddb_at_spectralogic.com> wrote in message
news:97b61782.0210301000.75832c9b_at_posting.google.com...


> Thanks, this makes sense.  My only problem is that I cannot write this


> as a view in MS SQL server.  Any one know why?  It simply errors out

> and complains that there are no output columns or from clause.

>

> Todd

>

> "Tibor Karaszi" <tibor_not_pressed_ham_.karaszi_at_cornerstone.se> wrote in

message news:<Ewvu9.1454$1r1.44981_at_newsc.telia.net>...


> > Todd,


> >

> > Here's a "brute force" method, using some string manipulation and a

UNION.


> > Dialect is MS Transact-SQL. The CHARINDEX function find the position of


the


> > first expression within the second expression.


> >

> > SELECT

> >  order_number

> > ,sales_rep

> > ,item

> > ,quantity

> > ,LEFT(territories, CHARINDEX(' ', territories))

> > FROM sales_orders

> > UNION ALL

> > SELECT

> >  order_number

> > ,sales_rep

> > ,item

> > ,quantity

> > ,RIGHT(territories, CHARINDEX(' ', territories))

> > FROM sales_orders

> >

> > --

> > Tibor Karaszi