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.