March 12, 2007 at 9:57 am
I have an Access query that I would like to change into a SQL view (or stored proc - I'm not sure which would be correct). The access query is as follows:
TRANSFORM Max(vw_PIs_Reps_Prelim.Name) AS Name
SELECT tblHSPCMain.File
FROM vw_PIs_Reps_Prelim RIGHT JOIN tblHSPCMain ON vw_PIs_Reps_Prelim.File = tblHSPCMain.File
GROUP BY tblHSPCMain.File
PIVOT vw_PIs_Reps_Prelim.Role;
I am having trouble getting the TRANSFORM and PIVOT working, and I read somewhere that they are not recognized in SQL. Is there a way to create a SQL view that will achieve the same thing?
March 12, 2007 at 2:36 pm
I was going to give this a try, but admittedly, I don't know a whole lot about Access. There's an article on pivot tables in Books Online that might help you. I can post the text of it if you don't have access to BOL, but other than that, I don't know if I can help you that much. Perhaps this post would get more replies in the Microsoft Access forum? You could try posting your question there.
March 13, 2007 at 6:26 am
You can find a pretty good discussion of this issue at this link: http://www.databasejournal.com/features/mssql/article.php/3516331
I hope this helps to solve your problem!
March 13, 2007 at 1:26 pm
Here is a stored proc that creates pivot data:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE [createpivot]
@dbname varchar(8000),
@pivotrows varchar(8000),
@pivottable varchar(8000),
@pivotdata varchar(8000),
@pivotcols varchar(8000),
@pivotfunc varchar(8000)
as declare
@sqltxt varchar(8000),
@sqlstmt varchar(8000),
@pivotcolumn varchar(8000)
/*set pivotrow names here comma delimited
set @dbname = 'WEB_Reports'
set @pivotrows = 'tylddate'
set @pivottable = 'vw_dlycntsumbyleadtype'
set @pivotdata = 'tyldcount'
set @pivotcols = 'lead_type'
set @pivotfunc = 'sum'
do not edit below this line*/
exec('use ' + @dbname)
set @sqltxt = 'select '+@pivotrows+','
set @sqlstmt = 'declare pivot_cursor cursor for select distinct('+@pivotcols+') from '+@pivottable
exec(@sqlstmt)
open pivot_cursor
fetch next from pivot_cursor
into @pivotcolumn
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
set @sqltxt = @sqltxt + ' ' + @pivotfunc + '(case '+ @pivotcols +' when '''+ @pivotcolumn + ''' then '+ @pivotdata +' else 0 end) as ['+ @pivotcolumn +'],'
FETCH NEXT FROM pivot_cursor
into @pivotcolumn
END
CLOSE pivot_cursor
DEALLOCATE pivot_cursor
set @sqltxt = left(@sqltxt,len(@sqltxt)-1)
set @sqltxt = @sqltxt + 'from ' + @pivottable + ' group by ' + @pivotrows + ' order by ' + @pivotrows
/*print @sqltxt*/
exec(@sqltxt)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
March 15, 2007 at 12:54 pm
Thanks a lot for the suggstions! It helps!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply