none
Convert an Access crosstab query to T-SQL (SQL Server)

    Question

  • Transform Count(1) as Count
    Select Cust,[Cust#],EntryDate,CloseDate
    from Tbl1,Dates
    where EntryDate>=[start date]
    Group by Cust,[Cust#],EntryDate,CloseDate
    Order by EntryDate
    Pivot Quote;
     I am having difficulty understanding  Access Crosstab query and conversion  to T-SQL, Any help is appreciated.

    Srikanth

    Thursday, October 17, 2013 1:06 AM

Answers

  • You dont have direct way of doing this in T-SQL unless you apply dynamic sql

    DECLARE @QuoteList varchar(max),@SQL varchar(max)
    
    SELECT @QuoteList = STUFF((SELECT DISTINCT',[' + Quote + ']' FROM Tbl1 FOR XML PATH('')),1,1,'')
    
    SET @SQL = 'SELECT Cust,[Cust#],EntryDate,CloseDate,' + @QuoteList +'
    FROM
    (
    SELECT *
    FROM Tbl1,Dates
    WHERE Entrydate > =[start date]
    )m
    PIVOT(COUNT(1) FOR Quote IN ('+ @QuoteList + '))p'
    
    EXEC (@SQL)

    • Proposed as answer by RSingh() Thursday, October 17, 2013 3:31 PM
    • Marked as answer by D S Kanth Friday, October 18, 2013 12:08 PM
    Thursday, October 17, 2013 3:39 AM
  • Hello Srikanth,

    In T-SQL you can use a PIVOT query to solve it; see Using PIVOT and UNPIVOT


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by RSingh() Thursday, October 17, 2013 3:31 PM
    • Marked as answer by D S Kanth Friday, October 18, 2013 12:08 PM
    Thursday, October 17, 2013 4:11 AM
  • Refer the below example. It simply group the strings in each rows saperated by comma. i.e it will return a string of "Quote" saperated by comma.

    DECLARE @TEMP TABLE(ID INT, [VALUE] NVARCHAR(30))
    INSERT INTO @TEMP VALUES(1 ,  'MAZ')
    INSERT INTO @TEMP VALUES(1 ,   'HON')
    INSERT INTO @TEMP VALUES(1 ,   'FOR')
    INSERT INTO @TEMP VALUES(2  ,  'JEEP')
    INSERT INTO @TEMP VALUES(2 ,   'CHE')
    INSERT INTO @TEMP VALUES(3 ,   'NIS')
    INSERT INTO @TEMP VALUES(4 ,   'GMC')
    INSERT INTO @TEMP VALUES(4 ,   'ACC')
    INSERT INTO @TEMP VALUES(4 ,   'LEX')
    --------------------WITH STUFF FUNCTION
    SELECT [id], 
           Stuff((SELECT ',' + [VALUE] 
                  FROM   @TEMP 
                  WHERE  [id] = a.[id] 
                  FOR xml path('')), 1, 1, '') [VALUE]
    FROM   @TEMP a 
    GROUP  BY  [id]
    STUFF function has 4 parameters.

    Regards, RSingh


    • Edited by RSingh() Friday, October 18, 2013 2:17 AM
    • Marked as answer by D S Kanth Friday, October 18, 2013 12:11 PM
    Friday, October 18, 2013 2:16 AM

All replies

  • You dont have direct way of doing this in T-SQL unless you apply dynamic sql

    DECLARE @QuoteList varchar(max),@SQL varchar(max)
    
    SELECT @QuoteList = STUFF((SELECT DISTINCT',[' + Quote + ']' FROM Tbl1 FOR XML PATH('')),1,1,'')
    
    SET @SQL = 'SELECT Cust,[Cust#],EntryDate,CloseDate,' + @QuoteList +'
    FROM
    (
    SELECT *
    FROM Tbl1,Dates
    WHERE Entrydate > =[start date]
    )m
    PIVOT(COUNT(1) FOR Quote IN ('+ @QuoteList + '))p'
    
    EXEC (@SQL)

    • Proposed as answer by RSingh() Thursday, October 17, 2013 3:31 PM
    • Marked as answer by D S Kanth Friday, October 18, 2013 12:08 PM
    Thursday, October 17, 2013 3:39 AM
  • Hello Srikanth,

    In T-SQL you can use a PIVOT query to solve it; see Using PIVOT and UNPIVOT


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by RSingh() Thursday, October 17, 2013 3:31 PM
    • Marked as answer by D S Kanth Friday, October 18, 2013 12:08 PM
    Thursday, October 17, 2013 4:11 AM
  • I am not able to understand the "Tbl1 FOR XML PATH('')),1,1,'')" I do not have a XML Path for this table 


    Srikanth

    Friday, October 18, 2013 1:26 AM
  • Refer the below example. It simply group the strings in each rows saperated by comma. i.e it will return a string of "Quote" saperated by comma.

    DECLARE @TEMP TABLE(ID INT, [VALUE] NVARCHAR(30))
    INSERT INTO @TEMP VALUES(1 ,  'MAZ')
    INSERT INTO @TEMP VALUES(1 ,   'HON')
    INSERT INTO @TEMP VALUES(1 ,   'FOR')
    INSERT INTO @TEMP VALUES(2  ,  'JEEP')
    INSERT INTO @TEMP VALUES(2 ,   'CHE')
    INSERT INTO @TEMP VALUES(3 ,   'NIS')
    INSERT INTO @TEMP VALUES(4 ,   'GMC')
    INSERT INTO @TEMP VALUES(4 ,   'ACC')
    INSERT INTO @TEMP VALUES(4 ,   'LEX')
    --------------------WITH STUFF FUNCTION
    SELECT [id], 
           Stuff((SELECT ',' + [VALUE] 
                  FROM   @TEMP 
                  WHERE  [id] = a.[id] 
                  FOR xml path('')), 1, 1, '') [VALUE]
    FROM   @TEMP a 
    GROUP  BY  [id]
    STUFF function has 4 parameters.

    Regards, RSingh


    • Edited by RSingh() Friday, October 18, 2013 2:17 AM
    • Marked as answer by D S Kanth Friday, October 18, 2013 12:11 PM
    Friday, October 18, 2013 2:16 AM