Crosstab query in SQL Server? 1

[slybitz]

I have a crosstab query in access but i need this query in SQL Server but SQL does not support crosstabs, as you probably know. How can I convert this crosstab to be useful in SQL Server? Thank you very much for your help, I'm lost.

The Crosstab Query:

TRANSFORM Sum(sCAN_BreadVelocity.PSPW) AS SumOfPSPW
SELECT sCAN_BreadVelocity.DC, sCAN_BreadVelocity.Item, sCAN_BreadVelocity.Vendor, sCAN_BreadVelocity.DCName, sCAN_BreadVelocity.ItemDescription, Avg(sCAN_BreadVelocity.PSPW) AS AveragePSPW
FROM sCAN_BreadVelocity
GROUP BY sCAN_BreadVelocity.DC, sCAN_BreadVelocity.Item, sCAN_BreadVelocity.Vendor, sCAN_BreadVelocity.DCName, sCAN_BreadVelocity.ItemDescription
PIVOT sCAN_BreadVelocity.STC_EffectiveDate;

sCAN_BreadVelocity:

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
SET ANSI_WARNINGS OFF 
GO


-- exec sCAN_BreadVelocity '3/24/2003'
ALTER   PROCEDURE dbo.sCAN_BreadVelocity
 (
   @i_EndDate datetime
 )
AS 

SET NOCOUNT ON

CREATE TABLE #temp
 (
   DC                                 nvarchar,
   EndDate                            datetime, 
   DCName                             nvarchar, 
   Item                               nvarchar, 
   ItemDescription                    nvarchar, 
   PSPW                               decimal (15,4), 
   STC_EffectiveDate                  datetime, 
   Vendor                             nvarchar (255), 
   MonthHeader                        datetime, 
   Cases                              decimal (15,4), 
   STC_StoreCount                     int
 )

INSERT INTO #temp
 (
   DC,
   EndDate, 
   DCName, 
   Item, 
   ItemDescription, 
   PSPW, 
   STC_EffectiveDate, 
   Vendor, 
   MonthHeader, 
   Cases, 
   STC_StoreCount
 )

SELECT 
   tCAN_Velocity.DC,
   @i_EndDate, 
   tCAN_DCs.DCName, 
   tCAN_Velocity.Item, 
   tCAN_Matrix.[Desc] AS ItemDescription, 
   dbo.GetPSPW(Cases,STC_StoreCount,STC_EffectiveDate) AS PSPW,
   vCAN_StoreCount.STC_EffectiveDate, 
   IsNull(tCAN_Matrix.Vendor,"Unspecified") AS Vendor, 
--    Format([STC_EffectiveDate],"mmm yyyy") 
--    AS 
--    MonthHeader, :
   right(convert(char(11),STC_EffectiveDate,113),8) AS MonthHeader,
   tCAN_Velocity.Cases, 
   vCAN_StoreCount.STC_StoreCount

FROM 
   ((vCAN_StoreCount 
   INNER 
   JOIN tCAN_Velocity 
   ON 
   (vCAN_StoreCount.STC_IDDC = tCAN_Velocity.DC) 
   AND 
   (vCAN_StoreCount.MonthEff = tCAN_Velocity.Month) 
   AND 
   (vCAN_StoreCount.YearEff = tCAN_Velocity.Year)) 
   INNER JOIN 
   tCAN_DCs 
   ON 
   tCAN_Velocity.DC = tCAN_DCs.DC) 
   LEFT JOIN 
   tCAN_Matrix 
   ON 
   (tCAN_Velocity.DC = tCAN_Matrix.DC) 
   AND 
   (tCAN_Velocity.Item = tCAN_Matrix.GFSNo) 
   AND 
   (tCAN_Velocity.Year = tCAN_Matrix.Year) 
   AND 
   (tCAN_Velocity.Month = tCAN_Matrix.Month)

WHERE 
   (
      (
         vCAN_StoreCount.STC_EffectiveDate
         Between 
         DateAdd(m,-11,@i_EndDate) 
         And 
         @i_EndDate
      ) 
         AND 
         (
            IsNull(tCAN_Matrix.Vendor,"Unspecified") 
            In ('Gerards','Turano','UpperCrust','Canada')
         )
   )

ORDER BY 
   tCAN_Velocity.DC, 
   tCAN_Velocity.Item, 
   vCAN_StoreCount.STC_EffectiveDate

SELECT
   *
FROM
   #temp



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 

[NoCoolHandle]

Do a search on "Cross-Tab Reports" in BooksOnLine (BOL).

Then next version of SQL has an actuall Crosstab clause, but it is possible to write crosstab type querys in the current releases.

The key is agregating multiple case statements..


HTH

Rob

 

[checkai]

i've used this...

--crosstabD 'select chargeacid from sgtVKProjectBudgetStaff inner join tblstaff on sgtVKProjectBudgetStaff.staffid = tblstaff.staffid  group by chargeacid','sum(hours)','tblstaff.forename','sgtVKProjectBudgetStaff left join tblstaff on sgtVKProjectBudgetStaff.staffid = tblstaff.staffid left join tblstaffType on sgtVKProjectBudgetStaff.staffTypeid = tblstaffType.staffTypeid'
--SELECT STATEMENT,SUMMARY CALCULATION,PIVOT COLUMN,TABLE NAME
CREATE PROCEDURE crosstabD
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(200), 
@table varchar(200) 
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' 
+ @pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
WHEN 0 THEN '' ELSE '''' END 
FROM tempdb.information_schema.columns 
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + 
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' 
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[slybitz]

Thanks checkai but how do I incorporate that into my query? Do I call it? If so, how so?

Thanks again!