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

smile

SOLVED CLOSED ACTIVE ANSWERED
User: smile
Posted: on Dec 05, 2018 01:11 AM
Forum: SQL Server
Answer: 2
Views: 2742

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

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

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on Dec 05, 2018 03:50 AM
Report

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.

dharmend...

0 4

Reply
ANSWER
User: dharmendr
Replied: on Dec 06, 2018 12:37 AM
Report

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

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

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

Merge Rows having NULL values in SQL Server

Company

Explore

Follow us

Contact Us