Join 2 colums

29 Jun 2006 8:23 PM

blackduke77

Hi can anyone help me out here, i have a table which I am importing data into usin SQL 2000 DTS,it has a number of columns but i am only interested in the two shown below.

Table name as400DocData

PageNum    OrderNumber
1          123456
2          123456
1          654321
2          654321

I am trying to create a view that out put one column thats has these two merged together but the page number being a three figure numer ie 001 as shown below.

View name AS400Recon

DocNum
123456001
123456002
654321001
654321002

Thanks for any help in advnced -- blackduke77 ------------------------------------------------------------------------ Posted via http://www.codecomments.com ------------------------------------------------------------------------

30 Jun 2006 2:24 AM

Tracy McKibben

blackduke77 wrote:
Show quote

> Hi can anyone help me out here, i have a table which I am importing data
> into usin SQL 2000 DTS,it has a number of columns but i am only
> interested in the two shown below.
>
> Table name as400DocData
>
> PageNum    OrderNumber
> 1          123456
> 2          123456
> 1          654321
> 2          654321
>
> I am trying to create a view that out put one column thats has these
> two merged together but the page number being a three figure numer ie
> 001 as shown below.
>
> View name AS400Recon
>
> DocNum
> 123456001
> 123456002
> 654321001
> 654321002
>
> Thanks for any help in advnced
>
>
>
> --
> blackduke77
> ------------------------------------------------------------------------
> Posted via http://www.codecomments.com
> ------------------------------------------------------------------------
>

Without knowing datatypes or lengths, here's one way:

SELECT RTRIM(CONVERT(VARCHAR(10), OrderNumber)) + RIGHT('000' +
RTRIM(CONVERT(VARCHAR(5), PageNum)), 3)