March 29, 2016 at 2:40 pm
--This is what I am trying to create
PROD_CODECODE 1CODE 2CODE 3
----------------------------------
Prod 1 TrueTrueFalse
Prod 2 FalseTrueTrue
Prod 3 TrueTrueTrue
--This is what I have right now
PROD_CODECODE 1CODE 2CODE 3
----------------------------------
Prod 1 Code 1NULLNULL
--First of all I need all the possible combinations of #Master_Products and #Shipping_Codes which is why I created the cross join
--I am trying to determine how to show which combination of #tblMasterShipping.Prod_code and #Shipping_Codes.Ship_Code
--that exist in #tblMasterShipping in the pivot table
--There is a one to many relation from #Master_Products to #tblMasterShipping
--There is a one to many relation from #Shipping_Codes to #tblMasterShipping
--so if a record exists in #tblMasterShipping with the combination of #Master_Products.Prod_code and #Shipping_Codes.Ship_Code
--I want the CODE 1 column of the pivot table to be True and if it does not exist I want it to be false
--the example below shows me all the records in #tblMasterShipping and that is not what I need
CREATE TABLE #tblMasterShipping(
[Prod_Code] [char](8) NOT NULL,
[Ship_Code] [smallint] NOT NULL,
[Base_Price] [money] NULL)
CREATE TABLE #Shipping_Codes(
[Ship_Code] [smallint] NOT NULL,
[Descr] [varchar](50) NOT NULL,
[Active] [char](1) NOT NULL)
CREATE TABLE #Master_Products(
[Prod_Code] [char](8) NOT NULL,
[Base_Component] [smallint] NOT NULL,
[Descr] [varchar](35) NOT NULL)
Insert into #Shipping_Codes select 1,'Code 1','1'
Insert into #Shipping_Codes select 2,'Code 2','1'
Insert into #Shipping_Codes select 3,'Code 3','1'
Insert into #Master_Products select 'Prod 1','1','Product Code 1'
Insert into #Master_Products select 'Prod 2','1','Product Code 2'
Insert into #Master_Products select 'Prod 3','1','Product Code 3'
Insert into #tblMasterShipping select 'Prod 1',1,1
Select #Master_Products.Prod_Code, #Shipping_Codes.Ship_Code, #Shipping_Codes.Descr
into #AllRecords
from #Shipping_Codes
cross join #Master_Products
where #Shipping_Codes.Active = '1'
order by #Master_Products.Prod_Code
Select Prod_Code, [Code 1], [Code 2], [Code 3] from
(
Select a.Prod_Code, a.Descr
from #AllRecords a
left outer join #tblMasterShipping b on a.Prod_Code = b.Prod_Code and a.Ship_Code = b.Ship_Code
where b.Ship_Code is not null
)
up
pivot
(
MAX(Descr)
FOR Descr in ([Code 1],[Code 2],[Code 3])
) as pvt
Order by Prod_Code
--select * from #tblMasterShipping
--select * from #Shipping_Codes
--select * from #Master_Products
--drop table #tblMasterShipping
--drop table #Shipping_Codes
--drop table #Master_Products
March 29, 2016 at 3:33 pm
First, the WHERE clause in your subquery is turning your LEFT OUTER JOIN into an INNER JOIN. The purpose of using the LEFT OUTER JOIN in the first place is so that you can determine which records have a match on the left side, but not on the right side.
Second, you are only returning columns from the left side of your LEFT OUTER JOIN in your subquery. Your main query needs to have access to fields from the RIGHT side of the to determine whether those fields are NULL (representing no match). You do need to be careful if you have records where the fields are NULLABLE so that you can distinguish between a missing match and a matched record with a NULL value in that field.
Third, your sample shipping table has only ONE RECORD. Given your criteria and the single row, exactly ONE CELL can be TRUE. There is no way to produce your desired results with the sample data that you have given.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 29, 2016 at 3:53 pm
Thanks for responding.
I made this change to the main query
Select Prod_Code, [Code 1], [Code 2], [Code 3] from
(
Select a.Prod_Code, a.Descr, b.Prod_Code'MSProdCode', b.Ship_Code'MSShipCode'
from #AllRecords a
left outer join #tblMasterShipping b on a.Prod_Code = b.Prod_Code and a.Ship_Code = b.Ship_Code
)
up
pivot
(
MAX(Descr)
FOR Descr in ([Code 1],[Code 2],[Code 3])
) as pvt
Order by Prod_Code
This is my results but still not exactly what I am trying for. I really only want the second, third and forth rows based on what I want my intended end result to be. Does that make sense?
Prod_CodeCode 1Code 2Code 3
Prod 1 NULL Code 2Code 3
Prod 1 Code 1NULL NULL
Prod 2 Code 1Code 2Code 3
Prod 3 Code 1Code 2Code 3
The number of records I have in #tblMasterShipping is correct I obviously will have more than that in the future.
As I stated previously, I am trying to give a user a way to see all the possible combinations of the MasterProducts table and the ShippingCode table and look in the tblMasterShipping table to determine what combinations have been added to the table and which combinations have not been added. Do you have another suggestion if what I am trying to do cant be done?
March 30, 2016 at 7:45 am
This issue has been resolved. Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy