4 Replies Latest reply on Oct 9, 2017 8:31 PM by Deepak Rai

    Building a table with duplicate values and displaying ALL values

    Bryan Mills

      OK, I have what I expect is a fairly simple resolution but it's been stumping me for FAR longer than I care to admit


      I have a table in my SQL Server database that looks like this but with far more rows:




      I am trying to replicate this table on a worksheet to display all of this data and then display the scores for each item in an added column at the end by linking on the question_id which is located in a 2nd datasource that contains the scores. Problem is, when there are the same questions that appear in different indicator_id's (as the 1st and 3rd row above show) I cannot get anything to show up. There's another table I bring in that has the text for each of the question id's (question_id and text the only 2 fields, joined on the question_id) and in one of those rows it is going to display Null (lets say the one for principle 1) while the other (for principle 5) will display the question text. Or, when messing around I can get it to display all of the principles and all of the indicator_ids for which there is a single row, but it then aggregates all the question_id's with multiple entries in a new row that shows * for all but the question_id.


      This same issue is also causing me a ton of trouble when building sets as trying to reference the sets via new dimension that spins out the principles (built off the question_id) fails in the same way (the questions appearing in both sets do not appear.) But I've given up on trying to use sets to accomplish this and moved on to dumping everything but the data into one table that looks like what you see above.


      Any idea on how to solve this so I can simply build a table that looks identical to above displaying each question_id anywhere it appears?