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

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

Constant re-indexing of db

We have a database that on a daily basis suffers from poor performance.

Our IT folks found through trial and error that running a complete DBCC reindex job on the database at those times acts as a "release valve" and the server / database start performing normally again.

I don't think that the OLAP activity on this database is anything major so not a whole lot of data is being added. Plus page splitting activity is minimal at the time so I don't think the indexes need to be re-built.

I was thinking that maybe the re-index job is causing deadlocks and therefore eliminating blocked processes. But sp_locks show nothing is being blocked prior to running the re-index job.

Can you throw ideas about how re-indexing or its byproduct effects could be affecting database performance?

Also, what should I trace? What additional logging can SQL put into the errorlog? What are performance impacts in production of performing TRaces of that kind?

This wouldn't be a denial of service type attack would it?

Please send all your suggestions and I'll look into them.

Thanks

...Ray

See if this helps: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
|||A side-effect of creating an index is to create fullscan statistics. You may actually be suffering from statistics that are not getting updated often enough, and thus be getting bad query plans since the optimizer is making decisions with stale information. Be sure that auto-create and auto-update statistics are enabled. If that is not good enough, try running sp_updatestats nightly. If you can isolate just which tables have bad stats, then you can frequently update the statistics on those tables. If sampled statistics (the default) don't give you the desired results, then consider updating statistics with FULLSCAN.

See this white paper regarding statistics in SQL Server 2005.

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx