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 GOI want to have either the view or select where I would have phones listed as rows along with their types.
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?