Convert Access CROSSTAB query to SQL Table or View

I have a Crosstab query that I need to convert to SQL to complete upsize of a large DB.
I have a table (here referred to as Data) with the fields: Resource, Date and Count. I need to transform it to a table (or view) with a fields called Date, and one field for each Resource that exists in the Data table.

The Data table looks like this:
RES DATE COUNT
res1 Jan06 5
res2 Jan06 4
res3 Jan 06 2
res1 Feb06 9
res2 Feb06 5
res3 Feb06 7

etc

The Access crosstab query sql is:
=====================
TRANSFORM Sum(Data.Count) AS SumOfCount
SELECT Data.Date
FROM Data
GROUP BY Data.Date
ORDER BY Data.Date
PIVOT Data.Resource;

which gives the resultant data set for charting:
Date res1 res2 res3
Jan06 5 4 2
Feb06 9 5 7

TRANSFORM is not T-SQL. I assume I need a usp to create the required table. Any ideas please?

George Cooper.

In SQL Server, you can use either CASE statement to pivot the table or PIVOT function in SQL Server 2005.

Here is CASE solution:

SELECT sDate,
AVG(CASE WHEN res ='res1' THEN sCount END) as res1,
AVG(CASE WHEN res ='res2' THEN sCount END) as res2,
AVG(CASE WHEN res ='res3' THEN sCount END) as res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
WHERE res IN ('res1', 'res2', 'res3')
GROUP BY sDate
ORDER By Convert(DATETIME,'01'+sDate,13)

PIVOT solution:(SQL Server 2005)

SELECT sDate, res1, res2, res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
PIVOT (AVG(sCount) FOR res IN ([res1], [res2], [res3])) AS pvt
ORDER By Convert(DATETIME,'01'+sDate,13)

You need to pay attention to your so-called Date column. I convert the text (nvarchar) field to datetime for sorting purpose.

|||

Thanks,

Pivot soultion works well.

Regards\

George Cooper

|||There is an interesting article on this issue at:
http://tinyurl.com/mgrwo

|||

But if the number of destination columns (res1, res2, res3,...,resN) is unknown?

Using SqlServer 2000.

Later i found these great article:
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Convert Access CROSSTAB query to SQL Table or View

I have a Crosstab query that I need to convert to SQL to complete upsize of a large DB.
I have a table (here referred to as Data) with the fields: Resource, Date and Count. I need to transform it to a table (or view) with a fields called Date, and one field for each Resource that exists in the Data table.

The Data table looks like this:
RES DATE COUNT
res1 Jan06 5
res2 Jan06 4
res3 Jan 06 2
res1 Feb06 9
res2 Feb06 5
res3 Feb06 7

etc

The Access crosstab query sql is:
=====================
TRANSFORM Sum(Data.Count) AS SumOfCount
SELECT Data.Date
FROM Data
GROUP BY Data.Date
ORDER BY Data.Date
PIVOT Data.Resource;

which gives the resultant data set for charting:
Date res1 res2 res3
Jan06 5 4 2
Feb06 9 5 7

TRANSFORM is not T-SQL. I assume I need a usp to create the required table. Any ideas please?

George Cooper.

In SQL Server, you can use either CASE statement to pivot the table or PIVOT function in SQL Server 2005.

Here is CASE solution:

SELECT sDate,
AVG(CASE WHEN res ='res1' THEN sCount END) as res1,
AVG(CASE WHEN res ='res2' THEN sCount END) as res2,
AVG(CASE WHEN res ='res3' THEN sCount END) as res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
WHERE res IN ('res1', 'res2', 'res3')
GROUP BY sDate
ORDER By Convert(DATETIME,'01'+sDate,13)

PIVOT solution:(SQL Server 2005)

SELECT sDate, res1, res2, res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
PIVOT (AVG(sCount) FOR res IN ([res1], [res2], [res3])) AS pvt
ORDER By Convert(DATETIME,'01'+sDate,13)

You need to pay attention to your so-called Date column. I convert the text (nvarchar) field to datetime for sorting purpose.

|||

Thanks,

Pivot soultion works well.

Regards\

George Cooper

|||There is an interesting article on this issue at:
http://tinyurl.com/mgrwo

|||

But if the number of destination columns (res1, res2, res3,...,resN) is unknown?

Using SqlServer 2000.

Later i found these great article:
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Convert Access CROSSTAB query to SQL Table or View

TRANSFORM IIf(Sum(IIf([blockinyield]=True,[SIZE],0))>0,Sum([Y_TOTAL_ton])/Sum(IIf([blockinyield]=True,[SIZE],0)),0) AS Yield_THA
SELECT OILPALM.NAME, OILPALM.YEAR, formatyear([year]) AS yearDisplay, Count(OILPALM.BLOCK) AS CountOfBLOCK
FROM OILPALM
GROUP BY OILPALM.NAME, OILPALM.YEAR
PIVOT Year([D_PLANTED]);

how to convert the access query above to sql server 2000

In SQL Server 2000 you have't have predefined operator to get the PIVOT table..

Here you have to manually write the query to get the pivot result...

(Example)

Code Snippet

Create Table #BikeSales
(
Year int,
Product Varchar(100),
Sales Int
)

Insert Into #BikeSales Values ('2005', 'HONDA F1', 10000)
Insert Into #BikeSales Values ('2006', 'HONDA F1', 6000)
Insert Into #BikeSales Values ('2007', 'HONDA F1', 7000)

Insert Into #BikeSales Values ('2005', 'HONDA IRL', 100)
Insert Into #BikeSales Values ('2006', 'HONDA IRL', 99)
Insert Into #BikeSales Values ('2007', 'HONDA IRL', 1000)

Insert Into #BikeSales Values ('2005', 'HONDA MotoGP', 124)
Insert Into #BikeSales Values ('2006', 'HONDA MotoGP', 344)
Insert Into #BikeSales Values ('2007', 'HONDA MotoGP', 132)

Insert Into #BikeSales Values ('2005', 'HONDA Super GT', 234)
Insert Into #BikeSales Values ('2006', 'HONDA Super GT', 32344)
Insert Into #BikeSales Values ('2007', 'HONDA Super GT', 123232)

Select
[Main].Product
,Sum([2005].Sales) as [2005]
,Sum([2006].Sales) as [2006]
,Sum([2007].Sales) as [2007]
From (Select Distinct Product From #BikeSales) as [Main]
Left Outer Join (Select * From #BikeSales Where Year=2005) as [2005] On [2005].Product=[Main].Product
Left Outer Join (Select * From #BikeSales Where Year=2006) as [2006] On [2006].Product=[Main].Product
Left Outer Join (Select * From #BikeSales Where Year=2007) as [2007] On [2007].Product=[Main].Product
Group By [Main].Product

You can generate the above query dynamically using the following script..

Code Snippet

Declare @.JoinQuery as Varchar(1000);
Declare @.SelectQuery as Varchar(1000);
Declare @.PreparedJoinQuery as Varchar(1000);
Declare @.PreparedSelectQuery as Varchar(1000);
Select @.JoinQuery = '', @.SelectQuery = ''
Select @.PreparedJoinQuery = 'Left Outer Join (Select * From #BikeSales Where Year=?) as [?] On [?].Product=[Main].Product '
Select @.PreparedSelectQuery =',Sum([?].Sales) as [?]'
Select
@.JoinQuery = @.JoinQuery + Replace(@.PreparedJoinQuery,'?',Cast(year as Varchar))
,@.SelectQuery = @.SelectQuery + Replace(@.PreparedSelectQuery,'?',Cast(year as Varchar)) From #BikeSales Group By Year

Exec ('Select [Main].Product' + @.SelectQuery + ' From (Select Distinct Product From #BikeSales) as [Main]' + @.JoinQuery + ' Group By [Main].Product')

|||

Using Manivannan's data, this method of creating a 'pivot' table in SQL 2005 is quite a bit more efficient. (Single Pass, No JOINS, NO Sub-Queries, No Dynamic SQL.)

Code Snippet

DECLARE @.BikeSales table
( [Year] int,
Product varchar(25),
Sales int
)

Insert Into @.BikeSales Values ('2005', 'HONDA F1', 10000)
Insert Into @.BikeSales Values ('2006', 'HONDA F1', 6000)
Insert Into @.BikeSales Values ('2007', 'HONDA F1', 7000)
Insert Into @.BikeSales Values ('2005', 'HONDA IRL', 100)
Insert Into @.BikeSales Values ('2006', 'HONDA IRL', 99)
Insert Into @.BikeSales Values ('2007', 'HONDA IRL', 1000)
Insert Into @.BikeSales Values ('2005', 'HONDA MotoGP', 124)
Insert Into @.BikeSales Values ('2006', 'HONDA MotoGP', 344)
Insert Into @.BikeSales Values ('2007', 'HONDA MotoGP', 132)
Insert Into @.BikeSales Values ('2005', 'HONDA Super GT', 234)
Insert Into @.BikeSales Values ('2006', 'HONDA Super GT', 32344)
Insert Into @.BikeSales Values ('2007', 'HONDA Super GT', 123232)

Select
Product,
[2005] = sum( CASE [Year] WHEN 2005 THEN Sales END ),
[2006] = sum( CASE [Year] WHEN 2006 THEN Sales END ),
[2007] = sum( CASE [Year] WHEN 2007 THEN Sales END )
FROM @.BikeSales
GROUP BY Product
ORDER BY Product

Convert Access CROSSTAB query to SQL Table or View

I have a Crosstab query that I need to convert to SQL to complete upsize of a large DB.
I have a table (here referred to as Data) with the fields: Resource, Date and Count. I need to transform it to a table (or view) with a fields called Date, and one field for each Resource that exists in the Data table.

The Data table looks like this:
RES DATE COUNT
res1 Jan06 5
res2 Jan06 4
res3 Jan 06 2
res1 Feb06 9
res2 Feb06 5
res3 Feb06 7

etc

The Access crosstab query sql is:
=====================
TRANSFORM Sum(Data.Count) AS SumOfCount
SELECT Data.Date
FROM Data
GROUP BY Data.Date
ORDER BY Data.Date
PIVOT Data.Resource;

which gives the resultant data set for charting:
Date res1 res2 res3
Jan06 5 4 2
Feb06 9 5 7

TRANSFORM is not T-SQL. I assume I need a usp to create the required table. Any ideas please?

George Cooper.

In SQL Server, you can use either CASE statement to pivot the table or PIVOT function in SQL Server 2005.

Here is CASE solution:

SELECT sDate,
AVG(CASE WHEN res ='res1' THEN sCount END) as res1,
AVG(CASE WHEN res ='res2' THEN sCount END) as res2,
AVG(CASE WHEN res ='res3' THEN sCount END) as res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
WHERE res IN ('res1', 'res2', 'res3')
GROUP BY sDate
ORDER By Convert(DATETIME,'01'+sDate,13)

PIVOT solution:(SQL Server 2005)

SELECT sDate, res1, res2, res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
PIVOT (AVG(sCount) FOR res IN ([res1], [res2], [res3])) AS pvt
ORDER By Convert(DATETIME,'01'+sDate,13)

You need to pay attention to your so-called Date column. I convert the text (nvarchar) field to datetime for sorting purpose.

|||

Thanks,

Pivot soultion works well.

Regards\

George Cooper

|||There is an interesting article on this issue at:
http://tinyurl.com/mgrwo

|||

But if the number of destination columns (res1, res2, res3,...,resN) is unknown?

Using SqlServer 2000.

Later i found these great article:
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Conversion of procedure making crosstab to function

Hello Everybody,
I have the following problem. I found procedure alolowing me to create
dynamic crosstab and it works fine, but I cannot save the results as a
table. Is there any way to do this? Maybe someone is in possesion of
function which works as below procedure? Or someone is able to change
this procedure to function which returns table which could be used with
command CREATE TABLE?
Procedure looks as follows:
CREATE PROCEDURE sp_TRANSFORM
/*
Purpose: Creates a Pivot(tm) table for the specified table,
view or select statement
Author: svenh@.itrain.de
Version: 1.1
History: march 2000 version 1.0
july 2002 version 1.1
Input parameters:
@.Aggregate_Function (optional)
the aggregate function to use for the pivot
default function is SUM
@.Aggregate_Column
name of column for aggregate
@.TableOrView_Name
name of table or view to use
if name contains spaces or other special
characters [] should be used
Can also be a valid SELECT statement
@.Select_Column
Column for first column in result table
for this column row values are displayed
@.Pivot_Column
Column that is transformed into columns
for this column column values are displayed
@.DEBUG
Set this flag to 1 to get debug-information
Example usage:
Table given aTable
content: Product Salesman Sales
P1 Sa 12
P2 Sb 10
P2 Sb 3
P3 Sa 12
P1 Sc 8
P3 Sa 1
P2 Sa NULL
CALL
EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
or EXEC sp_Transform @.Aggregate_Column='Sales',
@.TableOrViewName='aTable',
@.Select_Column='Product',
@.Pivot_Column='Salesman'
Result:
Product| Sa | Sb | Sc | Total
--+--+--+--+--
P1 | 12,00 | 0,00 | 8,00 | 20,00
P2 | 0,00 | 13,00 | 0,00 | 13,00
P3 | 13,00 | 0,00 | 0,00 | 13,00
--+--+--+--+--
Total | 25,00 | 13,00 | 8,00 | 46,00
*/
@.Aggregate_Function nvarchar(30) = 'SUM',
@.Aggregate_Column nvarchar(255),
@.TableOrView_Name nvarchar(255),
@.Select_Column nvarchar(255),
@.Pivot_Column nvarchar(255),
@.DEBUG bit = 0
AS
SET NOCOUNT ON
DECLARE @.TransformPart nvarchar(4000)
DECLARE @.SQLColRetrieval nvarchar(4000)
DECLARE @.SQLSelectIntro nvarchar(4000)
DECLARE @.SQLSelectFinal nvarchar(4000)
IF @.Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG',
'STDEV', 'VAR', 'VARP', 'STDEVP')
BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1,
@.Aggregate_Function) END
ELSE
BEGIN
SELECT @.SQLSelectIntro = 'SELECT CASE WHEN (GROUPING(' +
QUOTENAME(@.Select_Column) +
') = 1) THEN ''Total'' ELSE ' +
'CAST( + ' +
QUOTENAME(@.Select_Column) +
' AS NVARCHAR(255)) END As ' +
QUOTENAME(@.Select_Column) +
', '
IF @.DEBUG = 1 PRINT @.sqlselectintro
SET @.SQLColRetrieval =
N'SELECT @.TransformPart = CASE WHEN @.TransformPart IS NULL THEN ' +
N'''' + @.Aggregate_Function + N'(CASE CAST(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N' AS VARCHAR(255)) WHEN ''' + CAST(' +
QUOTENAME(@.Pivot_Column) +
N' AS NVarchar(255)) + ''' THEN ' + @.Aggregate_Column
+
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N') ELSE @.TransformPart + '', ' + @.Aggregate_Function +
N' (CASE CAST(' + QUOTENAME(@.Pivot_Column) +
N' AS nVARCHAR(255)) WHEN ''' + CAST(' +
QUOTENAME(CAST(@.Pivot_Column As VarChar(255))) +
N' AS nVARCHAR(255)) + ''' THEN ' +
@.Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N') END FROM (SELECT DISTINCT ' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N' FROM ' + @.TableOrView_Name + ') SelInner'
IF @.DEBUG = 1 PRINT @.SQLColRetrieval
EXEC sp_executesql @.SQLColRetrieval,
N'@.TransformPart nvarchar(4000) OUTPUT',
@.TransformPart OUTPUT
IF @.DEBUG = 1 PRINT @.TransformPart
SET @.SQLSelectFinal =
N', ' + @.Aggregate_Function + N'(' +
CAST(@.Aggregate_Column As Varchar(255)) +
N') As Total FROM ' + @.TableOrView_Name + N'
GROUP BY ' +
@.Select_Column + N' WITH CUBE'
IF @.DEBUG = 1 PRINT @.SQLSelectFinal
EXEC (@.SQLSelectIntro + @.TransformPart + @.SQLSelectFinal)
END
GO
Thank you very much for any help,
Rafal
*** Sent via Developersdex http://www.examnotes.net ***You can try something like this...
INSERT INTO TableName
EXEC proc_name
For example
CREATE TABLE #HelpDB(name VARCHAR(255), db_size VARCHAR(255), owner
VARCHAR(255), dbid INT, created VARCHAR(255), status VARCHAR(255),
compatibility_level VARCHAR(255))
INSERT INTO #HelpDB
EXEC sp_helpdb
SELECT *
FROM #HelpDB
DROP TABLE #HelpDB
"Rafal Ba" <ash@.robertjanowski.pl> wrote in message
news:ONdDCvbmFHA.2904@.tk2msftngp13.phx.gbl...
> Hello Everybody,
> I have the following problem. I found procedure alolowing me to create
> dynamic crosstab and it works fine, but I cannot save the results as a
> table. Is there any way to do this? Maybe someone is in possesion of
> function which works as below procedure? Or someone is able to change
> this procedure to function which returns table which could be used with
> command CREATE TABLE?
> Procedure looks as follows:
> CREATE PROCEDURE sp_TRANSFORM
> /*
> Purpose: Creates a Pivot(tm) table for the specified table,
> view or select statement
> Author: svenh@.itrain.de
> Version: 1.1
> History: march 2000 version 1.0
> july 2002 version 1.1
> Input parameters:
> @.Aggregate_Function (optional)
> the aggregate function to use for the pivot
> default function is SUM
> @.Aggregate_Column
> name of column for aggregate
> @.TableOrView_Name
> name of table or view to use
> if name contains spaces or other special
> characters [] should be used
> Can also be a valid SELECT statement
> @.Select_Column
> Column for first column in result table
> for this column row values are displayed
> @.Pivot_Column
> Column that is transformed into columns
> for this column column values are displayed
> @.DEBUG
> Set this flag to 1 to get debug-information
> Example usage:
> Table given aTable
> content: Product Salesman Sales
> P1 Sa 12
> P2 Sb 10
> P2 Sb 3
> P3 Sa 12
> P1 Sc 8
> P3 Sa 1
> P2 Sa NULL
> CALL
> EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
> or EXEC sp_Transform @.Aggregate_Column='Sales',
> @.TableOrViewName='aTable',
> @.Select_Column='Product',
> @.Pivot_Column='Salesman'
> Result:
> Product| Sa | Sb | Sc | Total
> --+--+--+--+--
> P1 | 12,00 | 0,00 | 8,00 | 20,00
> P2 | 0,00 | 13,00 | 0,00 | 13,00
> P3 | 13,00 | 0,00 | 0,00 | 13,00
> --+--+--+--+--
> Total | 25,00 | 13,00 | 8,00 | 46,00
>
> */
> @.Aggregate_Function nvarchar(30) = 'SUM',
> @.Aggregate_Column nvarchar(255),
> @.TableOrView_Name nvarchar(255),
> @.Select_Column nvarchar(255),
> @.Pivot_Column nvarchar(255),
> @.DEBUG bit = 0
> AS
> SET NOCOUNT ON
> DECLARE @.TransformPart nvarchar(4000)
> DECLARE @.SQLColRetrieval nvarchar(4000)
> DECLARE @.SQLSelectIntro nvarchar(4000)
> DECLARE @.SQLSelectFinal nvarchar(4000)
> IF @.Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG',
> 'STDEV', 'VAR', 'VARP', 'STDEVP')
> BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1,
> @.Aggregate_Function) END
> ELSE
> BEGIN
> SELECT @.SQLSelectIntro = 'SELECT CASE WHEN (GROUPING(' +
> QUOTENAME(@.Select_Column) +
> ') = 1) THEN ''Total'' ELSE ' +
> 'CAST( + ' +
> QUOTENAME(@.Select_Column) +
> ' AS NVARCHAR(255)) END As ' +
> QUOTENAME(@.Select_Column) +
> ', '
> IF @.DEBUG = 1 PRINT @.sqlselectintro
> SET @.SQLColRetrieval =
> N'SELECT @.TransformPart = CASE WHEN @.TransformPart IS NULL THEN ' +
> N'''' + @.Aggregate_Function + N'(CASE CAST(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N' AS VARCHAR(255)) WHEN ''' + CAST(' +
> QUOTENAME(@.Pivot_Column) +
> N' AS NVarchar(255)) + ''' THEN ' + @.Aggregate_Column
> +
> N' ELSE 0 END) AS '' + QUOTENAME(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N') ELSE @.TransformPart + '', ' + @.Aggregate_Function +
> N' (CASE CAST(' + QUOTENAME(@.Pivot_Column) +
> N' AS nVARCHAR(255)) WHEN ''' + CAST(' +
> QUOTENAME(CAST(@.Pivot_Column As VarChar(255))) +
> N' AS nVARCHAR(255)) + ''' THEN ' +
> @.Aggregate_Column +
> N' ELSE 0 END) AS '' + QUOTENAME(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N') END FROM (SELECT DISTINCT ' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N' FROM ' + @.TableOrView_Name + ') SelInner'
> IF @.DEBUG = 1 PRINT @.SQLColRetrieval
> EXEC sp_executesql @.SQLColRetrieval,
> N'@.TransformPart nvarchar(4000) OUTPUT',
> @.TransformPart OUTPUT
> IF @.DEBUG = 1 PRINT @.TransformPart
> SET @.SQLSelectFinal =
> N', ' + @.Aggregate_Function + N'(' +
> CAST(@.Aggregate_Column As Varchar(255)) +
> N') As Total FROM ' + @.TableOrView_Name + N'
> GROUP BY ' +
> @.Select_Column + N' WITH CUBE'
> IF @.DEBUG = 1 PRINT @.SQLSelectFinal
> EXEC (@.SQLSelectIntro + @.TransformPart + @.SQLSelectFinal)
> END
> GO
>
> Thank you very much for any help,
> Rafal
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Unfortunately, I can't make new table because number of her columns is
not constant in time. Probably, there is in all crosstables.
I have solved this problem as follows:
1) Create View from procedure using OPENROWSET
CREATE VIEW MyView AS
SELECT *
FROM OPENROWSET('SQLOLEDB','seattle1';'manage
r';'MyPass',
'EXEC MyProcedure')
2) Create table from view
SELECT * INTO NewTable FROM MyView
Maybe somebody has other idea?
*** Sent via Developersdex http://www.examnotes.net ***

Controls missing in toolbox

All in sudden all the Controls (Table, Crosstab, textbox...) disappeared from my Toolbox. I clicked View -> Toolbox and still it is empty.

Anyone has any idea what is going on..

Thanks.

Got it. I reset and got all back.

thanks.