Solved

SQL Pivot Result Into A Table

Posted on 2014-02-10
2
372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

726 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