Solved

SQL Pivot Result Into A Table

Posted on 2014-02-10
2
365 Views
Last Modified: 2014-02-10
I have a SP for making crosstab report. Which is displaying result perfectly. I only need result into a table XXX to use it further.

Execute Command
Begin
Exec CrossTabMake 'SELECT Transno,ExamCode,Cyear,PC,fn,Seckion,shift,StudentName,Grn, Roll_No,SN,ObtMarks FROM TempResult'
,'SN'
,'Max(ObtMarks)[]'
,'Transno,ExamCode,Cyear,PC,fn,Seckion,shift,StudentName,Grn, Roll_No'
,''
,'PC,Seckion,Shift,Roll_No'
End

Open in new window


Store procedure - SP
GO
/****** Object:  StoredProcedure [dbo].[CrossTabMake]    Script Date: 02/11/2014 11:10:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CrossTabMake] ( 
@SQL nvarchar(4000), 
@PivotCol varchar(100), 
@Summaries varchar(100), 
@GroupBy varchar(100), 
@OtherFields varchar(100) = '', 
@OrderByFields varchar(100) = '', 
@rollup bit = 0, 
@Debug bit = 0) 
AS 

set nocount on 
set ansi_warnings off 

declare @Vals varchar(8000); 
declare @Vals2 varchar(8000); 
declare @Vals3 varchar(8000); 
declare @tmp varchar(1000); 
declare @TotalLen int; 
declare @roll varchar(20); 

set @Vals = ''; 
set @Vals2 = ''; 
set @Vals3 = ''; 
set @TotalLen = len(@SQL) + len(@GroupBy) + Len(@OtherFields) + Len(@OrderByFields) 

if (@OtherFields <> '') 
begin 
set @OtherFields = ', ' + @OtherFields 
end 

if (@OrderByFields <> '') 
begin 
set @OrderByFields = ' order by ' + @OrderByFields 
end 

set @roll = CASE @rollup WHEN 1 THEN ' WITH ROLLUP' ELSE '' END 

create table #temp (thisPivot varchar(100)) 

insert into #temp 
exec ('select distinct convert(varchar(100),' + @PivotCol + ') as thisPivot FROM (' + @SQL + ') A') 
select @tmp = 
replace(replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' + replace(thisPivot,'''','''''') + 
''' THEN '),')[', ' END) as [' + thisPivot ), 
@TotalLen = @TotalLen + Len(@tmp), 
@Vals = case when @TotalLen < 7800 then @Vals + ', ' + @tmp else @Vals end, 
@Vals2 = case when @TotalLen between 7800 and 15799 then @Vals2 + ', ' + @tmp else @Vals2 end, 
@Vals3 = case when @TotalLen between 15800 and 23799 then @Vals3 + ', ' + @tmp else @Vals3 end 
from 
#Temp 
order by 
thisPivot 

drop table #Temp 

if (@Debug=0) 
exec( 'select ' + @GroupBy + @OtherFields + @Vals + @Vals2 + @Vals3 + 
' from (' + @SQL + ') A GROUP BY ' + @GroupBy + @roll + @OrderByFields) 
else 
begin 
print( 'select ' + @GroupBy + @OtherFields + @Vals + @Vals2 + @Vals3 + 
' from (' + @SQL + ') A GROUP BY ' + @GroupBy + @roll + @OrderByFields) 
end 

set nocount off 
set ansi_warnings on

Open in new window

0
Comment
Question by:Mehram
2 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 39849311
did you mean to say you need to insert tne result in third table something like this


Insert into table_XXX
 
exec( 'select ' + @GroupBy + @OtherFields + @Vals + @Vals2 + @Vals3 +
' from (' + @SQL + ') A GROUP BY ' + @GroupBy + @roll + @OrderByFields)
0
 

Author Comment

by:Mehram
ID: 39849312
yes,
Can you change my code accordingly.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question