I have a SQL select that I'm trying to set up as a View for use as a lookup in another script. This view has 14 columns, one of which is a 'RelationshipCode'. That can be one of 4 possible codes. My data currently has an Order Id, and 13 columns of data for a contact (Name, address, etc), but what I'd like is Order Id, and 12 columns of data for each of those 4 codes.
Current select:
SELECT [OC].[OrderId]
, [OC].[ContactFirstName]
, [OC].[ContactLastName]
, [OC].[Name]
, [OC].[Address1]
, [OC].[Address2]
, [OC].[City]
, [OC].[State]
, [OC].[ZIP]
, [OC].[RelationshipCode]
, [OC].[ContactLookUpCode]
, [OC].[ContactEmail]
, [OC].[IsMarketingSource]
, [CI].[UserID]
FROM [dbo].[OrderContact] AS OC
INNER JOIN [dbo].[Order] AS O
ON [O].[Id] = [OC].[OrderId]
LEFT JOIN [dbo].[ContactInformation] AS CI
ON [CI].[ContactData] = [OC].[ContactEmail]
LEFT JOIN [dbo].[Users] AS U
ON [U].[UserID] = [CI].[UserID]
AND [U].[Disabled] = 0
WHERE ( ( [OC].[RelationshipCode] = 'L' )
OR ( [OC].[RelationshipCode] = 'SB' )
OR ( [OC].[RelationshipCode] = 'LB' )
OR ( [OC].[RelationshipCode] = 'M' ) );
Example of the Order Contact Data
What I'm looking for is an output that has columns like so:
SELECT [OC].[OrderId]
, [OC].[ContactFirstName] AS [L_FirstName]
, [OC].[ContactLastName] AS [L_LastName]
, [OC].[Name] AS [L_Name]
, [OC].[Address1] AS [L_Address1]
, [OC].[Address2] AS [L_Address2]
, [OC].[City] AS [L_City]
, [OC].[State] AS [L_State]
, [OC].[ZIP] AS [L_Zip]
, [OC].[RelationshipCode] AS [L_RelationshipCode]
, [OC].[ContactLookUpCode] AS [L_LookupCode]
, [OC].[ContactEmail] AS [L_Email]
, [OC].[IsMarketingSource] AS [L_IsMarketingSource]
, [CI].[UserID] AS [L_UserId]
, [OC].[ContactFirstName] AS [SB_FirstName]
, [OC].[ContactLastName] AS [SB_LastName]
, [OC].[Name] AS [SB_Name]
, [OC].[Address1] AS [SB_Address1]
, [OC].[Address2] AS [SB_Address2]
, [OC].[City] AS [SB_City]
, [OC].[State] AS [SB_State]
, [OC].[ZIP] AS [SB_Zip]
, [OC].[RelationshipCode] AS [SB_RelationshipCode]
, [OC].[ContactLookUpCode] AS [SB_LookupCode]
, [OC].[ContactEmail] AS [SB_Email]
, [OC].[IsMarketingSource] AS [SB_IsMarketingSource]
, [CI].[UserID] AS [SB_UserId]
, [OC].[ContactFirstName] AS [LB_FirstName]
, [OC].[ContactLastName] AS [LB_LastName]
, [OC].[Name] AS [LB_Name]
, [OC].[Address1] AS [LB_Address1]
, [OC].[Address2] AS [LB_Address2]
, [OC].[City] AS [LB_City]
, [OC].[State] AS [LB_State]
, [OC].[ZIP] AS [LB_Zip]
, [OC].[RelationshipCode] AS [LB_RelationshipCode]
, [OC].[ContactLookUpCode] AS [LB_LookupCode]
, [OC].[ContactEmail] AS [LB_Email]
, [OC].[IsMarketingSource] AS [LB_IsMarketingSource]
, [CI].[UserID] AS [LB_UserId]
, [OC].[ContactFirstName] AS [M_FirstName]
, [OC].[ContactLastName] AS [M_LastName]
, [OC].[Name] AS [M_Name]
, [OC].[Address1] AS [M_Address1]
, [OC].[Address2] AS [M_Address2]
, [OC].[City] AS [M_City]
, [OC].[State] AS [M_State]
, [OC].[ZIP] AS [M_Zip]
, [OC].[RelationshipCode] AS [M_RelationshipCode]
, [OC].[ContactLookUpCode] AS [M_LookupCode]
, [OC].[ContactEmail] AS [M_Email]
, [OC].[IsMarketingSource] AS [M_IsMarketingSource]
, [CI].[UserID] AS [M_UserId]
The idea being that I'd have an order id, and all the contacts that went with it, on one record. (Trying to prevent some N+1 issues on the end-user-interface.)
