Insert Stored Procedure result to Temporary Table and Merge NULL value rows in SQL Server

Last Reply 7 days ago By dharmendr

Posted 8 days ago

 

tblSubjects

Subject ID

Subject Name

1

Eng/Urdu(W/Re/Dic)

2

Com/ISL(W/RE/Dic)

tblStudents

AdmissionNo

SName

FName

R-1

ABC

SKY

R-2

XYZ

ABC

R-3

SKY

XYZ

tblExam

AdmissionNo

SubjectID

MaxMarks

ObtainMarks

Month

R-1

1

100

50

December

R-2

1

100

60

December

R-3

1

100

44

December

R-1

2

100

56

December

R-2

2

100

53

December

R-3

2

100

36

December

DECLARE @DynamicPivotQuery ASNVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
DECLARE @ColSum ASNVARCHAR(MAX)

SELECT @cols=ISNULL(@cols +',','')+QUOTENAME(SubjectName)
FROM(SELECTDISTINCT SubjectName FROM tblTestSystem as sm innerjoin tblAssignSubjects as si on si.ASID = sm.SubjectID INNERJOIN tblDefSubject AS dsu ON si.SubjectID=dsu.SubjectID)AS Course

select @ColSum =isnull(@ColSum+',','')+'sum('+Quotename(SubjectName)+') as'+Quotename(SubjectName)
from(selectdistinct SubjectName from tblTestSystem as sm innerjoin tblAssignSubjects as si on si.ASID = sm.SubjectID INNERJOIN tblDefSubject AS dsu ON si.SubjectID=dsu.SubjectID)AS Course
DECLARE @qry NVARCHAR(4000)
SET @qry =
N'
;WITH cteStudentMarksDetails
AS ( SELECT ROW_NUMBER() OVER(ORDER BY(AdmissionNo))Row, TA.AdmissionNo
,(SELECT SName FROM tblStdReg TS WHERE TS.AdmissionNo = TA.AdmissionNo) as SName
,(SELECT FName FROM tblStdReg TS WHERE TS.AdmissionNo = TA.AdmissionNo) as FName
,(SELECT SPhone FROM tblStdReg TS WHERE TS.AdmissionNo = TA.AdmissionNo) as Phone
,SUM(Marks) ObtainedMarks
,SUM([MaxMarks]) TotalMarks
,Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) as Percentage
,(CASE WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 90 THEN ''A+''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 80 THEN ''A''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 70 THEN ''B''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 60 THEN ''C''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 50 THEN ''D''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 40 THEN ''E''
ELSE ''FAIL'' END ) AS Grade
,(CASE WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 90 THEN ''OutStanding''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 80 THEN ''Excellent''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 70 THEN ''Very Good''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 60 THEN ''Good''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 50 THEN ''Satisfactory''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 40 THEN ''Work Hard''
ELSE ''FAIL'' END ) AS Remarks
FROM tblTestSystem TA
GROUP BY AdmissionNo
)

SELECT distinct ROW_NUMBER() OVER(ORDER BY(AdmissionNo))Row,AdmissionNo
,SName
,FName
,Phone
, '+ @ColSum +'
,ObtainedMarks
,TotalMarks
,Percentage
,Grade

FROM (SELECT  ROW_NUMBER() OVER(ORDER BY(AdmissionNo))Row,AdmissionNo
,SubjectName
, Marks
,(SELECT ObtainedMarks FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as ObtainedMarks
,(SELECT Percentage FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as Percentage
,(SELECT TotalMarks FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as TotalMarks
,(SELECT Grade FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as Grade

,(SELECT SName FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as SName
,(SELECT FName FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as FName
,(SELECT Phone FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as Phone
FROM tblTestSystem as sm

inner join tblAssignSubjects as si on si.ASID = sm.SubjectID
INNER JOIN tblDefSubject AS dsu ON si.SubjectID=dsu.SubjectID

) p
PIVOT (Max(Marks) FOR SubjectName IN ('+@cols+')) AS Pvt  GROUP BY AdmissionNo,SName,FName,Phone,ObtainedMarks,TotalMarks,Percentage,Grade,'+@cols+''
EXECsp_executesql @qry;

query is written by taking reference from 

Merge multiple rows into one row by replacing NULL in dynamic Pivot query in SQL Server 

Showing output 

AdmissionNo

Eng/Urdu(W/Re/Dic)

Com/ISL(W/RE/Dic)

Total

Percentage

R-1

NULL

70

120

60

R-1

50

NULL

120

60

R-2

NULL

50

100

50

R-2

50

NULL

100

50

R-3

NULL

45

90

45

R-3

45

NLL

90

45

where as I require the output in the following format 

Required Output 

AdmissionNo

Eng/Urdu(W/Re/Dic)

Com/ISL(W/RE/Dic)

Total

Percentage

R-1

50

70

120

60

R-2

50

50

100

50

R-3

45

45

90

45

 

Posted 8 days ago

Hi smile,

Create a stored procedure of your dynamic query and then insert the stored procedure result to a temp table.

https://blog.sqlauthority.com/2013/05/27/sql-server-how-to-insert-data-from-stored-procedure-to-table-2-different-methods/

Then from the temptable merge the rows having null values.

Merge Rows having NULL values in SQL Server

 


Posted 7 days ago

Hi smile,

Create a stored procedure of your dynamic query which will return all the result having null rows.

Then create a Temp table and insert the stored procedure result set to the temp table and use select query to merge the null rows.

Check the below example. 

SQL

CREATE PROCEDURE TempDetails
AS
BEGIN
	DECLARE @Temp AS TABLE(AdmissionNo VARCHAR(10),[Eng/Urdu(W/Re/Dic)] INT,[Com/ISL(W/RE/Dic)] INT,Total INT,Percentage INT)
	INSERT INTO @Temp VALUES('R-1',NULL,70,120,60)
	INSERT INTO @Temp VALUES('R-1',50,NULL,120,60)
	INSERT INTO @Temp VALUES('R-2',NULL,50,100,50)
	INSERT INTO @Temp VALUES('R-2',50,NULL,100,50)
	INSERT INTO @Temp VALUES('R-3',NULL,45,90,45)
	INSERT INTO @Temp VALUES('R-3',45,NULL,90,45)
	SELECT * FROM @Temp
END

CREATE TABLE #Temp(AdmissionNo VARCHAR(10),[Eng/Urdu(W/Re/Dic)] INT,[Com/ISL(W/RE/Dic)] INT,Total INT,Percentage INT)
INSERT INTO #Temp
EXEC TempDetails 
SELECT AdmissionNo,MAX([Eng/Urdu(W/Re/Dic)]) [Eng/Urdu(W/Re/Dic)],MAX([Com/ISL(W/RE/Dic)]) [Com/ISL(W/RE/Dic)],MAX(Total) Total,MAX(Percentage) Percentage 
FROM #Temp
GROUP BY AdmissionNo
DROP TABLE #Temp

Output

AdmissionNo Eng/Urdu(W/Re/Dic) Com/ISL(W/RE/Dic) Total Percentage
R-1 50 70 120 60
R-2 50 50 100 50
R-3 45 45 90 45