How to select same column four times with separate column with different conditions I try but represent data repeatedly

Question

1.00/5 (1 vote)

See more:

select * from (select DISTINCT  s.Regno, dbo.subjects.Dsubject as Sub8, dbo.RESULT.total as totalSub8
                            FROM dbo.Student S  INNER JOIN
                       dbo.RESULT ON s.Regno = dbo.RESULT.REGNO INNER JOIN
                      dbo.subjects ON dbo.RESULT.SUBJECTID = dbo.subjects.ID INNER JOIN
                      dbo.Sessions ON s.Session = dbo.Sessions.ID 
                           WHERE      (dbo.RESULT.SEMESTER = '8th'))w outer apply 
 
            ( select DISTINCT s.Regno,dbo.subjects.Dsubject as sub7 ,dbo.RESULT.total as totalSub7
                            FROM dbo.Student s INNER JOIN
                      dbo.RESULT ON s.Regno = dbo.RESULT.REGNO INNER JOIN
                      dbo.subjects ON dbo.RESULT.SUBJECTID = dbo.subjects.ID INNER JOIN
                      dbo.Sessions ON s.Session = dbo.Sessions.ID 
                              WHERE (dbo.RESULT.SEMESTER = '7th'))s outer apply
                              
            ( select DISTINCT s.Regno, dbo.subjects.Dsubject as sub6 ,dbo.RESULT.total as totalSub6
                            FROM dbo.Student s INNER JOIN
                      dbo.RESULT ON s.Regno = dbo.RESULT.REGNO INNER JOIN
                      dbo.subjects ON dbo.RESULT.SUBJECTID = dbo.subjects.ID INNER JOIN
                      dbo.Sessions ON s.Session = dbo.Sessions.ID 
                              WHERE (dbo.RESULT.SEMESTER = '6th'))a outer apply
             ( select  DISTINCT  s.Regno,dbo.subjects.Dsubject as sub5 ,dbo.RESULT.total as totalSub5
                            FROM dbo.Student s INNER JOIN
                       dbo.RESULT ON s.Regno = dbo.RESULT.REGNO INNER JOIN
                      dbo.subjects ON dbo.RESULT.SUBJECTID = dbo.subjects.ID INNER JOIN
                      dbo.Sessions ON s.Session = dbo.Sessions.ID 
                              WHERE (dbo.RESULT.SEMESTER = '5th'))b

What I have tried:

the problem is retrieving data repeatedly

Posted 13-Feb-22 16:09pm

Muhammad Esa

Updated 15-Feb-22 3:23am

v4

Add a Solution

Comments

Maciej Los 14-Feb-22 5:22am

What's your issue?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2022-02-15T03:23:00

To demonstrate what is going on with all those cross apply statements consider this simple example:

SQL

create table #t1 (regno int, result int, semester int)
insert into #t1 values
(1, 10, 6),(1, 15, 7),(1, 20, 8),(1, 5, 5),
(2, 4, 5),(2, 6, 6),(2, 8, 7),(2, 10, 8);

If I add in just one cross apply

SQL

select * from
(
	(select regno, result, semester
	from #t1 
	where semester = 5) a
	cross apply 
	(select regno, result,semester
	from #t1 
	where semester = 6) b
)

I get the following returned

regno	result	semester	regno	result	semester
1		5		5			1		10		6
2		4		5			1		10		6
1		5		5			2		6		6
2		4		5			2		6		6

I've ended up with a row for regno=1, semester=5 cross applied to regno-1, semester=6 and another for regno=1, semester=5 cross applied to regno=2, semester=6
You can see an article on Cross Apply here - SQL Server CROSS APPLY and OUTER APPLY[^]

I suspect what you are trying to do is to get results like this:

Regno	5	6	7
1		5	10	15
2		4	6	8

where the 5, 6 and 7 across the top are the semesters (it's not good practice to store these as 5th, 6th, 7th).

To do that you need to use a PIVOT operator, see SQL Server PIVOT Operator Explained Clearly By Practical Examples[^]. In my simple example that would look like this

SQL

select * from
(
	select Regno, semester, result as totalSub
	FROM #t1
	WHERE SEMESTER in (5,6,7,8)
) src
PIVOT
(
	sum(totalSub) for Semester in ([5],[6],[7])
) pvt

And for your tables would look something like this (untested)

SQL

select * from 
(
		select DISTINCT  s.Regno, r.SEMESTER, sj.Dsubject as Sub8, r.total as totalSub
        FROM Student S  
		INNER JOIN RESULT r ON s.Regno = dbo.RESULT.REGNO 
		INNER JOIN subjects sj ON r.SUBJECTID = sj.ID 
		INNER JOIN Sessions ss ON s.Session = ss.ID 
        WHERE      (r.SEMESTER in ('5th','6th','7th','8th'))
)w 
PIVOT
(
	SUM(totalSub) for SEMESTER IN (5th,6th,7th,8th)
) p

M Imran Ansari · Answer 2 · 2022-02-14T01:08:00

Solution 1

You are looking merge functionality on crystal report. You need to merge two column to show your design. To merge cells, you need to concatenate two fields in your designer. To achieve this, you can simply create a formula and add two fields in that and display the formula. For more help you can check:
c# - How to Merge columns in crystal report - Stack Overflow[^]

Posted 14-Feb-22 1:08am

M Imran Ansari

Comments

CHill60 15-Feb-22 8:27am

Where did you get "crystal report" from this question. It's tagged SQL and the code is SQL. The OP has never posted a question about crystal reports. Completely off-topic