1

Topic: Repeating lines

Kind time of days. At a request output, at the student it is some tests with results. In each line the student, the test, and its result is displayed. If the student transited some tests there will be some lines with the same student and with other test. It would be desirable as that all to unite means SQL in one line. I.e. the student, the test result, the test 2 result. How it can be made? If it is necessary I can show the written request

2

Re: Repeating lines

go to FAQ

3

Re: Repeating lines

t1975453 wrote:

If it is necessary I can show the written request

Show.
The number of tests is restricted?

4

Re: Repeating lines

982183 wrote:

Show.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE procedure [dbo]. [sp_StudentsTesting]
@key varchar (8);
@id_group varchar (max)
as
with query (student,id_groups,groups,last,first,middle,branch,theme,question, [right], [all]) as
(
select st.id_student [student];
eg.id [id_groups];
eg.name [groups];
sts.last_name [last];
sts.first_name [first];
sts.middle_name [middle];
tt.id_branch [branch];
tq.id_testing_theme [theme];
stq.id_testing_question [question];
sum (stq.is_right) [right];
count (stq.is_right) [all]
from EducationGroup eg
left join EducationPlanGroup epg on epg.id_education_group=eg.id
left join Students sts on sts.id_group=epg.id
left join StudentsTesting st on st.id_student=sts.id
left join StudentsTestingQuestion stq on stq.id_students_testing=st.id
left join TestingQuestion tq on tq.id=stq.id_testing_question
left join TestingTheme tt on tt.id=tq.id_testing_theme
where st.is_education=0 and stq. [status] =1
and (
(len (@key) =4 and @key=convert (varchar, YEAR (st.date_start)))
or (len (@key) =6 and @key=convert (varchar, YEAR (st.date_start)) +right (' 0 ' +convert (varchar, month (st.date_start)), 2))
or (len (@key) =8 and @key=convert (varchar, YEAR (st.date_start)) +right (' 0 ' +convert (varchar, month (st.date_start)), 2) +right (' 0 ' +convert (varchar, day (st.date_start)), 2))
)
and eg.id = id_group
group by st.id_student,eg.id,eg.name,sts.last_name,sts.first_name,sts.middle_name,tt.id_branch,tq.id_testing_theme,stq.id_testing_question
)
select CONVERT (varchar, q.student) [stud];
CONVERT (varchar, q.id_groups) [id_gr];
CONVERT (varchar, q.groups) [gr];
CONVERT (varchar, q.last) + ' ' +CONVERT (varchar, q.first) + ' ' +CONVERT (varchar, q.middle) [FIO];
CONVERT (varchar, q.theme) [id_theme];
CONVERT (varchar, q.branch) [id_branch_theme];
t.theme [name_theme];
SUM (q. [all]) [all_question];
SUM (q. [right]) [right_question];
convert (decimal (5,2), 100.0*SUM (q. [right])/SUM (q. [all])) [percent]
from query q
left join TestingTheme t on t.id=q.theme
group by q.student,q.id_groups,q.groups,q.last,q.first,q.middle,q.branch,q.theme,t.theme
982183 wrote:

the Number of tests is restricted?

Beyond all bounds. Dynamic pulls out titles of tests

5

Re: Repeating lines

...
And if through PIVOT to try?

6

Re: Repeating lines

It is a lot of letters.
Simplify the task to three fields - "the student, the test, and its result"
Show the decision.
But it seems without PIVOT not to manage here.

7

Re: Repeating lines

982183 wrote:

it is a lot of letters.
Simplify the task to three fields - "the student, the test, and its result"
Show the decision.

select CONVERT (varchar, q.last) + ' ' +CONVERT (varchar, q.first) + ' ' +CONVERT (varchar, q.middle) [FIO];
t.theme [name_theme];
convert (decimal (5,2), 100.0*SUM (q. [right])/SUM (q. [all])) [percent]
from query q
left join TestingTheme t on t.id=q.theme
group by q.last, q.first, q.middle, t.theme
982183 wrote:

But it seems without PIVOT not to manage here.

PIVOT the good operator, but he is not able to process dynamic. He should specify explicit columns

8

Re: Repeating lines

Did not understand whence the table t (Probably a title that)
I do not see, that the given request solved the task set in the beginning.
If PIVOT it is not pleasant, it is possible to collect unique "test", on them to create the table of appropriate structure, and then to fill it from initial.

9

Re: Repeating lines

t1975453;

wrote:

PIVOT the good operator, but he is not able to process dynamic. He should specify explicit columns

Dynamic sql + pivot?

10

Re: Repeating lines

t1975453;
Katz suggests to surrender () to tear on the client

11

Re: Repeating lines

iiyama;
I.e. to drag on the client I booze  and there already with it to work, it not seems to you that to put it mildly incorrectly?

12

Re: Repeating lines

aleksrov wrote:

iiyama;
I.e. to drag on the client I booze  and there already with it to work, it not seems to you that to put it mildly incorrectly?

You are surprised, but there is a mass of means which it and demand, beginning from simple PivotGrid to BI without  with ssas

13

Re: Repeating lines

TaPaK;
, clearly. But what for it to do when it is possible to solve all on the server.

14

Re: Repeating lines

aleksrov wrote:

TaPaK;
, clearly. But what for it to do when it is possible to solve all on the server.

Yes it is possible even to heat coffee on the server who forbids

15

Re: Repeating lines

t1975453 wrote:

PIVOT the good operator, but he is not able to process dynamic. He should specify explicit columns

Here made

16

Re: Repeating lines

I say lies. On the contrary made.