Dynamic SQL

Results 1 to 2 of 2

Thread: Dynamic SQL

  1. #1
    Join Date
    Dec 1969

    Default Dynamic SQL

    Could someone point me in the right direction on how to build a dynamic SQL statement. I am using the ACCESS SQL code from a crosstab query as my base. The problem is that I don&#039;t know how many SELECT or GROUP BY variable there might be, I was wondering if the SQL would be valid with NULL variable values.<BR><BR>Here is a sample of a query that could be generated at my page.<BR><BR>strSQL = "TRANSFORM Count([EMS Test].Load) AS CountOfLoad SELECT [EMS Test].Qtr, [EMS Test].College FROM [EMS Test] GROUP BY [EMS Test].Qtr, [EMS Test].College PIVOT [EMS Test].Dept"<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default Just basic string manipulation...

    Not completely relevant, but in the same vein at least:<BR> http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=82<BR><BR>Do something like, which assumes you get the GROUP BY field names as an array, from some source:<BR><BR>&#060;%<BR>groupbyNames = Array("foo","bar","foobar")<BR><BR>groupby = "[EMS Test]." & Join( groupbyNames, ",[EMS Test]." )<BR><BR>SQL = ".... GROUP BY " & groupby & " ... "<BR>%&#062;<BR><BR>Though there are dozens of other ways of accomplishing the same thing. But use JOIN and REPLACE whenever you can, for performance and paucity of code.<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts