Ok, this works

select cAddr1, cAddr2, cCity, cState, cZip, cStreetNumber, nID, 
                      nCompanyID, JobDescription, dtComplete, dtScheduled, 
                      dtBiddone, cContact, cast(right(cPhone,10) as int) as nType, left(cPhone,21) as cPhone 
                      from (select dbo.Address.cAddr1, dbo.Address.cAddr2, dbo.Address.cCity, dbo.Address.cState, dbo.Address.cZip, dbo.Address.cStreetNumber, dbo.Address.nID, 
                      dbo.JobHeader.nCompanyID, dbo.JobHeader.cDescription AS JobDescription, dbo.JobHeader.dtComplete, dbo.JobHeader.dtScheduled, 
                      dbo.JobHeader.dtBiddone, dbo.JobHeader.cContact, 
                      cast(dbo.JobHeader.cPhone1 as char(21)) + ' ' + cast(dbo.JobHeader.nPhtype1 as char(10)) as cPhone1, 
                      cast(dbo.JobHeader.cPhone2 as char(21)) + ' ' + cast(dbo.JobHeader.nPhtype2 as char(10)) as cPhone2, 
                      cast(dbo.JobHeader.cPhone3 as char(21)) + ' ' + cast(dbo.JobHeader.nPhtype3 as char(10)) as cPhone3, 
                      cast(dbo.JobHeader.cPhone4 as char(21)) + ' ' + cast(dbo.JobHeader.nPhtype4 as char(10)) as cPhone4, 
                      cast(dbo.JobHeader.cPhone5 as char(21)) + ' ' + cast(dbo.JobHeader.nPhtype5 as char(10)) as cPhone5, 
                      cast(dbo.JobHeader.cPhone6 as char(21))+ ' ' + cast(dbo.JobHeader.nPhtype6 as char(10)) as cPhone6
                      FROM         dbo.Address INNER JOIN
                      dbo.AddressCategories ON dbo.Address.nAddressCategoryID = dbo.AddressCategories.nAddressCategoryID AND 
                      dbo.AddressCategories.cDescription = 'Job' INNER JOIN
                      dbo.JobHeader ON dbo.Address.nID = dbo.JobHeader.nJobHeaderID
 ) pvt
                      UNPIVOT (cPhone FOR Phone in (cPhone1, cPhone2, cPhone3, cPhone4, cPhone5, cPhone6))AS unpvt where LEFT(cPhone,21)>''

>Hi everybody,
>
>Here is a view I have now
>
>

>CREATE VIEW [dbo].[v_JobAddress]
>AS
>SELECT     dbo.Address.cAddr1, dbo.Address.cAddr2, dbo.Address.cCity, dbo.Address.cState, dbo.Address.cZip, dbo.Address.cStreetNumber, dbo.Address.nID, 
>                      dbo.JobHeader.nCompanyID, dbo.JobHeader.cDescription AS JobDescription, dbo.JobHeader.dtComplete, dbo.JobHeader.dtScheduled, 
>                      dbo.JobHeader.dtBiddone, dbo.JobHeader.cContact, dbo.JobHeader.cPhone1, dbo.JobHeader.nPhtype1, dbo.JobHeader.cPhone2, 
>                      dbo.JobHeader.nPhtype2, dbo.JobHeader.cPhone3, dbo.JobHeader.nPhtype3, dbo.JobHeader.cPhone4, dbo.JobHeader.nPhtype4, 
>                      dbo.JobHeader.cPhone5, dbo.JobHeader.nPhtype5, dbo.JobHeader.cPhone6, dbo.JobHeader.nPhtype6
>FROM         dbo.Address INNER JOIN
>                      dbo.AddressCategories ON dbo.Address.nAddressCategoryID = dbo.AddressCategories.nAddressCategoryID AND 
>                      dbo.AddressCategories.cDescription = 'Job' INNER JOIN
>                      dbo.JobHeader ON dbo.Address.nID = dbo.JobHeader.nJobHeaderID
>
>GO

>
>I want to have either the view or select where I would have phones listed as rows along with their types.
>
>I tried this SQL
>
>

>select cAddr1, cAddr2, cCity, cState, cZip, cStreetNumber, nID, 
>                      nCompanyID, JobDescription, dtComplete, dtScheduled, 
>                      dtBiddone, cContact, cPhone 
>                      from (select dbo.Address.cAddr1, dbo.Address.cAddr2, 
>dbo.Address.cCity, dbo.Address.cState, dbo.Address.cZip, dbo.Address.cStreetNumber, dbo.Address.nID, 
>                      dbo.JobHeader.nCompanyID, dbo.JobHeader.cDescription AS JobDescription, 
>dbo.JobHeader.dtComplete, dbo.JobHeader.dtScheduled, 
>                      dbo.JobHeader.dtBiddone, dbo.JobHeader.cContact, dbo.JobHeader.cPhone1, 
>dbo.JobHeader.nPhtype1, dbo.JobHeader.cPhone2, 
>                      dbo.JobHeader.nPhtype2, dbo.JobHeader.cPhone3, dbo.JobHeader.nPhtype3, dbo.JobHeader.cPhone4, dbo.JobHeader.nPhtype4, 
>                      dbo.JobHeader.cPhone5, dbo.JobHeader.nPhtype5, dbo.JobHeader.cPhone6, dbo.JobHeader.nPhtype6 
>                      FROM         dbo.Address INNER JOIN
>                      dbo.AddressCategories ON dbo.Address.nAddressCategoryID = dbo.AddressCategories.nAddressCategoryID AND 
>                      dbo.AddressCategories.cDescription = 'Job' INNER JOIN
>                      dbo.JobHeader ON dbo.Address.nID = dbo.JobHeader.nJobHeaderID
> ) pvt
>                      UNPIVOT (cPhone FOR Phone in (cPhone1, cPhone2, cPhone3, cPhone4, cPhone5, cPhone6))AS unpvt where cPhone>''

>
>which doesn't give me a phone type (which I can get from PhoneTypes table). Do you know how can I solve this problem except for 6 UNIONs?
>
>Thanks in advance.