SO I understand WHY I get the results I get but I am having a difficult time, most likely due to me, getting the results I want. So heres whats happening, I have a search that returns survey data. Each record returned has a bunch of data including a single QuestionID and QuestionAnswer. 1 question and answer per record. Most surveys have around 30 or so questions so 30 or so records per survey. I join in some other data to this. I get a table like this
rrname md_id data_det_q1 data_det_q2 data_det_q3 PII QuestionID QuestionAnswer count
Temp Name, Inc. Not Found 07/24/2018 0000-0003-3609 TRUE TRUE TRUE FALSE RR01 Temp Name, Inc Internal 1
Temp Name, Inc. Not Found 07/24/2018 0000-0003-3609 TRUE TRUE TRUE FALSE RR01a ['Other (please describe in Additional Comments below)'] 1
Temp Name, Inc. Not Found 07/24/2018 0000-0003-3609 TRUE TRUE TRUE FALSE RR01b 1- 10,000 1
Temp Name, Inc. Not Found 07/24/2018 0000-0003-3609 TRUE TRUE TRUE FALSE RR01c Daily 1
Temp Name, Inc. Not Found 07/24/2018 0000-0003-3609 TRUE TRUE TRUE FALSE RR02 Temp Name, Inc Internal 1
Temp Name, Inc. Not Found 07/24/2018 0000-0003-3609 TRUE TRUE TRUE FALSE RR02a ['Other (please describe in Additional Comments below)'] 1
Temp Name, Inc. Not Found 07/24/2018 0000-0003-3609 TRUE TRUE TRUE FALSE RR02b 1- 10,000 1
Temp Name, Inc. Not Found 07/24/2018 0000-0003-3609 TRUE TRUE TRUE FALSE RR02c Daily 1
Temp Name, Inc. Not Found 07/24/2018 0000-0003-3609 TRUE TRUE TRUE FALSE RR03 Yes 1
Temp Name, Inc. Not Found 07/24/2018 0000-0003-3609 TRUE TRUE TRUE FALSE RR04 Yes 0
I want to view this data horizontally in columns so I put it into pivot. So instead I get one row of all data split columns by QuestionID and column values of QuestionAnswer
so I get a pivot table like(this is 1 row of the table):
RR_Submission ID Created_By Created_On_Behalf_Of Temp Name_LOB Temp Name_Division Temp Name_Operating_Company Risk_Determination_Qu_1 Risk_Determination_Qu_2 Risk_Determination_Qu_3 Temp Name_Enterprise_Architect_Primary_Contact Temp Name_IT_Demand_Primary_Contact Temp Name_Procurement_Primary_Contact Temp Name_Project_Manager_Primary_Contact Temp Name_Security_Ambassador _Suite_Contract_ID Suite_Vendor Other_Contact_1 Other_Contact_2 Other_Contact_3 Other_Vendors Primary_Vendor_Contact_Email Primary_Vendor_Contact_Name Primary_Vendor_Contact_Phone Risk_Rank_Submit_Status Secondary_Vendor_Contact_Email Secondary_Vendor_Contact_Name Secondary_Vendor_Contact_Phone RR01 RR01a RR01b RR01c RR02 RR02a RR02b RR02c RR03 RR04 RR05 RR06 RR07 RR08 RR08a RR08b RR08c RR09 RR10 RR10a RR11 RR12 RR13 RR13a RR13b RR14 RR15 RR16 RR17 RR18 RR19 RR20 RR21
Temp Name, Inc 06/21/2018 0000-0000-1904 tasfasdf@srg.com NA NA NA NA FALSE TRUE FALSE NA NA Todd Peterman NA NA NA NA NA NA NA EPRI gsergfsdft@srr.com Ben Jammin Phillips 555-555-2728 Complete NA NA NA Temp Name Internal ['Other (please describe in Additional Comments below)'] 1- 10,000 Monthly Temp Name Internal ['Other (please describe in Additional Comments below)'] 10,001 - 100,000 Monthly Yes No Yes No No No N/A No > $1,000,000 No No Less than 4 Weeks No No No No 3-Jan 51% - 100% 1 Year - 5 Years
there are 2 fields I need to add to this table but when I do I get duplicate records because of how the data works. I have 2 fields Inbound and Outbound that are
Inbound = if (QuestionID = RR01a and QuestionAnswer="*PII*" ) then list the QuestionAnswer
Outbound if(QuestionID = RR02a and QuestionAnswer="*PII*") then list the QuestionAnswer
So what happens is all the surveys that dont have either one of those = true I get one line like I want but where those fields DO = an answer like PII I get 3 records: 1 record for Inbound, One record for Outbound and 1 record for the rest of the survey results. Heres an example:
RR_Submission rg_Risk_Rank Created_By Created_On_Behalf_Of AllTempiD_LOB AllTempiD_Division AllTempiD_Operating_Company Risk_Determination_Qu_1 Risk_Determination_Qu_2 Risk_Determination_Qu_3 AllTempiD_Enterprise_Architect_Primary_Contact AllTempiD_IT_Demand_Primary_Contact AllTempiD_Procurement_Primary_Contact AllTempiD_Project_Manager_Primary_Contact Suite_Contract_ID Suite_Vendor Inbound Outbound Other_Contact_1 Other_Contact_2 Other_Contact_3 Other_Vendors Primary_Vendor_Contact_Email Primary_Vendor_Contact_Name Primary_Vendor_Contact_Phone Secondary_Vendor_Contact_Email Secondary_Vendor_Contact_Name Secondary_Vendor_Contact_Phone RR01 RR01a RR01b RR01c RR02 RR02a RR02b RR02c RR03 RR04 RR05 RR06 RR07 RR08 RR08a RR08b RR08c RR09 RR10 RR10a RR11 RR12 RR13 RR13a RR13b RR14 RR15 RR16 RR17 RR18 RR19 RR20 RR21
AllTempiD, Inc. - 9872345 0000-0000-1308 twall@sec.com dbcooper@sec.com BD and Delivery NA NA FALSE TRUE FALSE NA NA Don Johnson John Potts NA NA None None NA NA NA NA joe.frank@sec.com Joe Frank 555-555-2356 NA NA NA AllTempiD Confidential Special Handling 1- 10,000 Daily AllTempiD Confidential Special Handling 1- 10,000 Daily No No No No No No No Mobile Solution Available No > $1,000,000 No No Less than 5 Days Yes Yes No No 10-Apr 51% - 100% 1 Year - 5 Years
AllTempiD, Inc. - 9872345 0000-0000-1308 twall@sec.com dbcooper@sec.com BD and Delivery NA NA FALSE TRUE FALSE NA NA Don Johnson John Potts NA NA None ['PII'] NA NA NA NA joe.frank@sec.com Joe Frank 555-555-2356 NA NA NA ['PII']
AllTempiD, Inc. - 9872345 0000-0000-1308 twall@sec.com dbcooper@sec.com BD and Delivery NA NA FALSE TRUE FALSE NA NA Don Johnson John Potts NA NA ['PII'] None NA NA NA NA joe.frank@sec.com Joe Frank 555-555-2356 NA NA NA ['PII']
Any ideas how I can get this to not split the records? I have tried all sorts of things, like a single field with a case statement but the issue is the Inbound/Outbound fields.
Thanks!
it would be easier to help if you shared your query
I'll wait one more day and then take this down, since no answers