Monday, August 1, 2016

Access_Crosstab_Query_to_TSQL


Converting an Access Crosstab query into SQL Server (T-SQL) is more difficult than you might think.  Take the following crosstab:

----------------------------------------------------------------------------------------
------  Access Crosstab Query   -----------------
----------------------------------------------------------------------------------------

/*
TRANSFORM Sum(Line_Days) AS SumOfLine_Days
SELECT Factory, Line, Sum(Line_Days) AS [Total Of Line_Days]
FROM Factory_Line_temp
GROUP BY Factory, Line
PIVOT DateID;

*/
 


In order do this in SQL Server, you need to build the SQL statement dynamically, adding the crosstab headers by reading them from the query, then building them in code. 



----------------------------------------------------------------------------------------
------ T-SQL Equivalent -----------------
----------------------------------------------------------------------------------------
DECLARE  @ColumnOrder  AS  TABLE(ColumnName 
                                 varchar(8)
                                 NOT NULL
                                 PRIMARY KEY)


DECLARE  @strSQL  AS NVARCHAR(4000)
INSERT INTO @ColumnOrder SELECT DISTINCT DateID FROM Factory_Line_temp


DECLARE  @XTabColumnNames  AS NVARCHAR(MAX)
DECLARE         @XTabColumn      AS varchar(20)
SET @XTabColumn = (SELECT MIN(ColumnName) FROM  @ColumnOrder)


SET @XTabColumnNames = N''


-- Create the xTab columns


WHILE (@XTabColumn IS NOT NULL)
  BEGIN
    SET @XTabColumnNames = @XTabColumnNames + N',' +
      QUOTENAME(CAST(@XTabColumn AS NVARCHAR(10)))
   SET @XTabColumn = (SELECT MIN(ColumnName)
                          FROM   @ColumnOrder
                          WHERE  ColumnName > @XTabColumn)
  END


SET @XTabColumnNames = SUBSTRING(@XTabColumnNames,2,LEN(@XTabColumnNames))


PRINT @XTabColumnNames


SET @strSQL = N'SELECT * FROM (
              SELECT Factory, Line, DateID, Line_Days
              FROM Factory_Line_temp) as header
              pivot (sum(Line_Days)
                    FOR DateID IN(' + @XTabColumnNames + N'))  AS Pvt '


PRINT @strSQL 


-- Execute strSQL sql

EXEC sp_executesql @strSQL




Monday, July 25, 2016

Access UPDATE query to SQL Server

How do I convert an Access UPDATE query to SQL Server?


Access SQL




  • UPDATE Books INNER JOIN UpdateList
       ON Books.ISBN = UpdateList.ISBN
       SET Books.Price = [Updatelist].[price]


SQL Server
  • UPDATE Books
        SET Books.Price = [Updatelist].[price]
       
        FROM Books INNER JOIN UpdateList
           ON Books.ISBN = UpdateList.ISBN




    Tuesday, July 19, 2016

    Access InStr() function to SQL Server equivalent


    What is the SQL Server equivalent to Access InStr() function?

    TargetField: String expression being searched
    SearchValue: String expression being sought
    StartPosition: Starting position for each search (optional)
    • InStr(TargetField, SearchValue)
    • InStr(StartPosition, TargetField, SearchValue)
    SQL Server
    • CHARINDEX(SearchValue, TargetField)
    • CHARINDEX(SearchValue, TargetField, StartPosition )

    Wednesday, July 13, 2016

    Access IIf() function to SQL Server equivalent

    What is the SQL Server equivalent to Access IIf() function?


    Access SQL
    • IIf([ResultValue]>=1, [Result Value], Null)


    SQL Server
    •  CASE WHEN [Result Value]>=1
    •    THEN [Result Value]
    •    ELSE NULL 
    •  END 

    Thursday, July 7, 2016

    Access NZ() function to SQL Server equivalent


    What is the SQL Server equivalent to Access NZ() function?


    Access SQL
    • NZ([Event Timestamp])


    SQL Server Equivalent
    • COALESCE([Event Timestamp],0)
    • ISNULL([Event Timestamp],0)
      • do not confuse this with the Access isnull() function.

    Tuesday, July 5, 2016

    Access CDate() function to SQL Server equivalent


    What is the SQL Server equivalent to Access CDate() function?


    Access SQL
    • CDate([Event Timestamp])


    SQL Server Equivalent
    • cast([Event Timestamp] as datetime)
    • convert(datetime, [Event Timestamp])

    Friday, July 1, 2016

    Access DateValue() function to SQL Server equivalent

    What is the SQL Server equivalent to Access DateValue() function?


    Access SQL
    • DateValue([Event Timestamp])


    SQL Server Equivalent
    • cast([Event Timestamp] as date)
    • convert(date, [Event Timestamp])