SQL query returning list of data that needs to be grouped

Question

I am trying to pull some data out of our testing database with an sql query and i am stuck. Below is a representation of how the data looks when I run it and how it should look once it is run.

What I get

Sample_Number  Centre_Code  DateEntered  AssayName
---------------------------------------------------
1              234          9/16/2010    TEST 1
1              234          9/16/2010    TEST 2
1              234          9/16/2010    TEST 3
1              234          9/16/2010    TEST 4
1              234          9/16/2010    TEST 5         
2              345          9/17/2010    TEST 1
2              345          9/17/2010    TEST 2
2              345          9/17/2010    TEST 3         
3              456          9/16/2010    TEST 1
3              456          9/16/2010    TEST 2
3              456          9/16/2010    TEST 3
3              456          9/16/2010    TEST 4
3              456          9/16/2010    TEST 5
3              456          9/16/2010    TEST 6
3              456          9/16/2010    TEST 7

What I am trying to get

Sample_Number  Centre_Code  DateEntered  ProfileName
-----------------------------------------------------                       
1              234          9/16/2010    PROFILE 1          
2              345          9/17/2010    PROFILE 2          
3              456          9/16/2010    PROFILE 3

The problem I am having is I have these testing profiles where a specific set of tests make up a profile for each sample and I cannot get them to group into profiles, I can only get the individual tests. I was thinking of using CASE in the query and specifying what each profiles tests are, but that doesn’t seem efficient and it is also beyond the limit of my sql knowledge. Here is the query I have so far.

SELECT     User.Sample.Sample_Number,User.Centre.Centre_Code, User.Sample.DateEntered, User.Assay.AssayName
FROM         User.Sample INNER JOIN
                      User.Centre ON User.Sample.Centre_ID = User.Centre.Centre_Id INNER JOIN
                      User.Profile ON User.Sample.Profile_ID = User.Profile.Profile_ID INNER JOIN
                      User.Batch ON User.Sample.Batch_ID = User.Batch.Batch_ID INNER JOIN
                      User.SampleResult ON User.Sample.Sample_ID = User.SampleResult.Sample_ID INNER JOIN
                      User.Assay INNER JOIN
                      User.Result ON User.Assay.Assay_ID = User.Result.Assay_ID ON 
                      User.SampleResult.Assay_ID = User.Assay.Assay_ID AND User.SampleResult.Result_ID = User.Result.Result_ID INNER JOIN
                      User.Test ON User.Profile.Profile_ID = User.Test.Profile_ID AND User.Assay.Assay_ID = User.Test.Assay_ID
WHERE     (User.Sample.DateEntered > CONVERT(DATETIME, '2010-09-01 23:59:59', 102)) AND (User.Sample.DateEntered < CONVERT(DATETIME, 
                      '2010-09-20 00:00:00', 102))
GROUP BY User.Sample.DateEntered, User.SampleResult.Sample_ID, User.Assay.AssayName, User.Centre.Centre_Code, 
                      User.Sample.Sample_ID, User.Sample.Sample_Number

For reference

Profile 1 = Test 1, Test 2, Test 3, Test 4, Test 5

Profile 2 = Test 1, Test 2, Test 3

Profile 3 = Test 1, Test 2, Test 3, Test 4, Test 5, Test 6, Test 7

Each test in a profile will have the same sample number, centre code, and date entered.

Is there anything I can do to the sql query or even in excel to manipulate it once I have run the query?

Answer 1

How about setting up the relationship between profiles and tests in another DB table and joining on that table? That way you could select/group on the profile name which is what you want, not the test name

Answer 2

You have too many fields in your GROUP BY clause. Remove User.Assay.AssayName from your GROUP BY clause. This should get you one step closer to what you're looking for. You might want to remove other fields from the GROUP BY clause too. I hope this helps.