Access Query to SQL

  • 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?

  • 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.

  • 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!

  • 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

     


  • 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