Need help in SQL query .Please

Question

I have a table like these.

+----------+-----------+
| UserId   |   Name    |
+----------+-----------+
| 1        |  Asar     |
| 2        |  Bozz     |
+----------+-----------+

and

+---------+-----------+-------------+
| TestId  |  UserId   |  TestResult |
+---------+-----------+-------------+
| 1       |  1        |  10.0       |
| 2       |  1        |  11.0       |
| 3       |  1        |  33         |
| 4       |  1        |  14.0       |
| 5       |  1        |  16.0       |
+---------+-----------+-------------+

Final result i need is :

+------------+-----------+-----------+-----------+-----------+----------+
| Name       |  Result1  |  Result2  |  Result3  |  Result4  |  Result5 |
+------------+-----------+-----------+-----------+-----------+----------+
| Asar       |  10.0     |  11.0     |  33       |  14.0     |  16.0    |
| Bozz       |  0        |  0        |  0        |  0        |  0       |
+------------+-----------+-----------+-----------+-----------+----------+

[Please read this article](http://whathaveyoutried.com) will be helpful — Barranka, Mar 08 '14 at 16:11
What is the highest # of tests there might be? Is it only 5? — Brian DeMilia, Mar 08 '14 at 16:12
Hi, i will xplain the scenario. There is 2 tables 1.user table and user's test_result table.I need to generate one report for every user's last top 5 test result in the fortmat(shown in the question).No repetition is allowed. — Vineeth MK, Mar 08 '14 at 16:34

score 0 · Answer 1 · answered Mar 08 '14 at 16:14

0

Providing the table structure, the SQL Query youre looking for is:

SELECT Name, TestResult
FROM <TABLE A>, <TABLE B>
WHERE <TABLE A>.UserId = <TABLE B>.UserId
GROUP BY Name

Although you should edit your post and add the names of the tables that you are using. Hope it helped!

answered Mar 08 '14 at 16:14

Alexander Pap.

27
4

There is only one testresult column in his source data, and he wants the output to show each testid as a column, with the testresult for each testid beneath (in different columns). This does not do that. – Brian DeMilia Mar 08 '14 at 16:19
Hi,Thanks for your fast response.but i need to pivot the result – Vineeth MK Mar 08 '14 at 16:19
@ShWiVeL When i saw your answer i thought that he might need something more, but i believe that his question was not that clear (At least for me :) ) – Alexander Pap. Mar 08 '14 at 16:23
Hi, i will xplain the scenario. There is 2 tables 1.user table and user's test_result table.I need to generate one report for every user's last top 5 test result in the fortmat(shown in the question).No repetition is allowed. – Vineeth MK Mar 08 '14 at 16:31

Brian DeMilia · Answer 2 · 2014-03-08T17:11:25.213

0

With the 2 tables named as tbl1 and tbl2 and under the assumption that there are only 5 tests:

select tbl1.name,
       test1.testresult as result1,
       test2.testresult as result2,
       test3.testresult as result3,
       test4.testresult as result4,
       test5.testresult as result5
  from tbl1
  join tbl2 test1
    on test1.userid = tbl1.userid
  join tbl2 test2
    on test2.userid = tbl1.userid
  join tbl2 test3
    on test3.userid = tbl1.userid
  join tbl2 test4
    on test4.userid = tbl1.userid
  join tbl2 test5
    on test5.userid = tbl1.userid
 where test1.testid =
       (select y.testid
          from tbl2 y
         where y.testresult = (select top 1 testresult
                                 from tbl2 x
                                where x.userid = y.userid
                                order by testresult desc))
   and test2.testid =
       (select y.testid
          from tbl2 y
         where y.testresult =
               (select min(testresult)
                  from (select top 2 testresult
                          from tbl2 x
                         where x.userid = y.userid
                         order by testresult desc)))
   and test3.testid =
       (select y.testid
          from tbl2 y
         where y.testresult =
               (select min(testresult)
                  from (select top 3 testresult
                          from tbl2 x
                         where x.userid = y.userid
                         order by testresult desc)))
   and test4.testid =
       (select y.testid
          from tbl2 y
         where y.testresult =
               (select min(testresult)
                  from (select top 4 testresult
                          from tbl2 x
                         where x.userid = y.userid
                         order by testresult desc)))
   and test5.testid =
       (select y.testid
          from tbl2 y
         where y.testresult =
               (select min(testresult)
                  from (select top 5 testresult
                          from tbl2 x
                         where x.userid = y.userid
                         order by testresult desc)))

edited Mar 08 '14 at 17:11

answered Mar 08 '14 at 16:15

Brian DeMilia

13,103
1
23
33

HI ShWiVeL, thank you for response, the "testid" is PK for the second table.so can't directly check.It might be varied.. – Vineeth MK Mar 08 '14 at 16:27
Okay, I can edit, but how many tests do you need to pivot? If it is only 5 you can add an inline view to find the testids for each user and then join to it, but the solution as a whole is only practical if you only want to summarize a few tests each time. Is that the case or no? – Brian DeMilia Mar 08 '14 at 16:30
Hi, i will xplain the scenario. There is 2 tables 1.user table and user's test_result table.I need to generate one report for every user's last top 5 test result in the fortmat(shown in the question).No repetition is allowed. – Vineeth MK Mar 08 '14 at 16:31
Hi, can we use a temp table in sql for creating the format – Vineeth MK Mar 08 '14 at 16:37
Just to confirm are you definitely using sql server 2008 and not 2012? I ask because the "top 5" scores for each user would be easy to obtain via windowing functions introduced in sql server 2012. – Brian DeMilia Mar 08 '14 at 16:40
i am using MSSQL 2008 – Vineeth MK Mar 08 '14 at 16:41
Hi ShWiVeL, any hope? – Vineeth MK Mar 08 '14 at 17:03
@VineethMK try the above, just make sure to replace all instances of tbl1 / tbl2 with your actual table names. – Brian DeMilia Mar 08 '14 at 17:05
I just made a couple of edits for bad aliases, if you already tried please recopy. – Brian DeMilia Mar 08 '14 at 17:07
I forgot the "Order by testresult desc" in each of the "top" subqueries. Please see edit. – Brian DeMilia Mar 08 '14 at 17:11
Hi ShWiVeL,Thanks for your effort. put another value in table 2 .some what like testid=6,userid=2,testresult=55 – Vineeth MK Mar 08 '14 at 18:21
What? The above query should grab the 5 highest scores for each user ID, then show the name in one column, and each of their "high scores" in the next 5 columns. – Brian DeMilia Mar 08 '14 at 18:28

Need help in SQL query .Please

2 Answers2