Showing posts with label crosstab. Show all posts
Showing posts with label crosstab. Show all posts

Sunday, March 25, 2012

Access Crosstab to an SQL Express PIVOT

I need some help in converting this crosstab SQL from an Access query to a View in SQL Server Express:

TRANSFORM First(tblPhones.PhoneNumber)AS FirstOfPhoneNumberSELECT tblPhones.ClientIDFROM tblPhonesGROUP BY tblPhones.ClientIDPIVOT tblPhones.PhoneType;

Hello:

You may need to change hardcoded phonetype in your case:

If this is not exact what you want, you can post some sample data and expected result here.

SELECT

ClientID,

MIN

(CASEWHEN PhoneType='office'THEN PhoneNumberEND)as OfficePhoneNumber,

MIN

(CASEWHEN PhoneType='home'THEN PhoneNumberEND)as HomePhoneNumber

FROM

(SELECT ClientID, PhoneType, PhoneNumberFROM tblPhones) p

WHERE

PhoneTypeIN('home','office')

GROUP

BY ClientID

--Or PIVOT solution:(SQL Server 2005)

SELECT

ClientID, office, home

FROM

(SELECT ClientID, PhoneType, PhoneNumberFROM tblPhones) p

PIVOT

(MIN(PhoneNumber)FOR PhoneTypeIN([office], [home]))AS pvt|||

I will give this try, but is it possible to do this without hard coding the phone type names? In the access query it just shows a gorup by list of used phonetypes.

|||

FYI. This is a version with dynamic pivot for your table.

SETNOCOUNTON

DECLARE @.TASTABLE(ynvarchar(10)NOTNULLPRIMARYKEY)

INSERTINTO @.TSELECTDISTINCT PhoneTypeFROM tblPhones

DECLARE @.T1ASTABLE(numintNOTNULLPRIMARYKEY)

DECLARE @.iASint

SET @.i=1

WHILE @.i<10

BEGIN

INSERTINTO @.T1SELECT @.i

SET @.i=@.i+1

END


DECLARE @.colsASnvarchar(MAX), @.yASnvarchar(10)

SET @.y=(SELECTMIN(y)FROM @.T)

SET @.cols= N''

WHILE @.yISNOTNULL

BEGIN

SET @.cols= @.cols+ N',['+CAST(@.yASnvarchar(10))+N']'

SET @.y=(SELECTMIN(y)FROM @.TWHERE y> @.y)

END

SET @.cols=SUBSTRING(@.cols, 2,LEN(@.cols))


DECLARE @.sqlASnvarchar(MAX)

SET @.sql= N'SELECT * FROM (SELECT ClientID, PhoneType, PhoneNumber FROM tblPhones) as t

PIVOT (min(PhoneNumber) FOR PhoneType IN('+ @.cols+ N')) AS pvt'

EXECsp_executesql @.sql

|||

You are obviously very good at this, I tried to test this and get a syntax error neer SET.

I tried to run the other PIVOT and received this type or message 'You must set your Compatibiltiy level higher (sp_dbcmptlevel)'. Does this mean that SQL Server 2005 Express does not support the PIVOT?

|||

Hello:

--You need to change the Compatibility level to SQL Server 2005 which is 90.

EXEC sp_dbcmptlevel yourDatabasename, 90;

--Or you can use SQL Server Management Studio (Express), right cilck on your database name to get the property window; under Options tab>> Compatibility level: ; you can choose from SQL Server 7.0(70), 2000(80), or 2005(90) from the dropdownbox.

|||

Ok, I set the compatibiltiy to 90, the first Pivot now works, but the dynamic PIVOT does not. I receive the following error:

The Set SQL construct or statement is not supported.

Syntax error near SET

Any thoughts.

|||

Here is one more piece that might help: I added the AS keyword to the start of the sproc and it saved (I really need it to be a view, but 1 thing at a time) it ran with the following error:

String or binary data would be truncated.

The statement has been terminated.

Incorrect syntax near ')'.

No rows affected.

|||I dont intend to steal limno's credit but to answer your question, you prbly have a variable that is beingset a value higher than what it can take. Increase the size of your string variable for which you are getting the error.|||

Ok, that helped I can save and run it as a sproce. I increased all of the nvarchars to (20).

I have tried to save it as a view and I get: 'The Set SQL construct or statement is not supported.' Then I get a incorrect syntax near keyword SET.

Any thoughts on this?

|||

Hello,

Could be this line?

@.TASTABLE(ynvarchar(10)NOTNULLPRIMARYKEY)

Please change the nvarchar(10) to nvarchar(50) or something bigger and try again.

If you have some sample data, I can test them from my end too.

|||

I set them to 20 and it works. Do you know how I could change this to allow me to save it as a view?

I very much appreciate your help,

|||Try to create the view from the query in the dynamical sql command, for example:

.
DECLARE @.sql AS nvarchar(MAX)

SET @.sql = N'CREATE VIEW v_test AS SELECT * FROM (SELECT ClientID, PhoneType, PhoneNumber FROM tblPhones) as t

PIVOT (min(PhoneNumber) FOR PhoneType IN(' + @.cols + N')) AS pvt'

SELECT * FROM v_Test|||

I tried your suggestion, but I get a SET not supported message and then a vw_DYPhoneList is an invalid name.

Any thoughts?

SETNOCOUNT ON DECLARE@.TAS TABLE(ynvarchar(20)NOT NULLPRIMARY KEY)INSERTINTO @.TSELECT DISTINCT Phone_TypeFROM tblPhonesDECLARE@.T1AS TABLE(numintNOT NULLPRIMARY KEY)DECLARE@.iAS int SET@.i=1WHILE@.i <20BEGININSERTINTO @.T1SELECT @.iSET@.i=@.i+1END--select * from @.T1DECLARE@.colsAS nvarchar(MAX), @.yAS nvarchar(20)SET@.y = (SELECT MIN(y)FROM @.T)SET@.cols = N''WHILE@.yISNOT NULLBEGINSET@.cols = @.cols + N',['+CAST(@.yAS nvarchar(20))+N']'SET@.y = (SELECT MIN(y)FROM @.TWHERE y > @.y)ENDSET@.cols =SUBSTRING(@.cols, 2,LEN(@.cols))DECLARE@.cols1AS nvarchar(MAX), @.numAS nvarchar(20)SET@.num = (SELECT MIN(num)FROM @.T1)SET@.cols1 = N''WHILE@.numISNOT NULLBEGINSET@.cols1 = @.cols1 + N',['+CAST(@.numAS nvarchar(20))+N']'SET@.num = (SELECT MIN(num)FROM @.T1WHERE num > @.num)ENDSET@.cols1 =SUBSTRING(@.cols1, 2,LEN(@.cols1))DECLARE@.cols2AS nvarchar(MAX), @.num2AS nvarchar(20)SET@.num2 = (SELECT MIN(num)FROM @.T1)SET@.cols2 = N'[1]+'WHILE@.num2ISNOT NULLBEGINIF@.num2>1SET@.cols2 = @.cols2 + N'coalesce('','''+ N'+['+CAST(@.num2AS nvarchar(20))+N'],'''')+ 'SET@.num2 = (SELECT MIN(num)FROM @.T1WHERE num > @.num2)ENDSET@.cols2 =SUBSTRING(@.cols2, 0,LEN(@.cols2))DECLARE @.sqlAS nvarchar(MAX)SET @.sql = N'CREATE VIEW vwDYPhoneList AS SELECT * FROM (SELECT ClientID, Phone_Type, Phone_Number FROM tblPhones) as tPIVOT (min(Phone_Number) FOR Phone_Type IN(' + @.cols + N')) AS pvt'SELECT *FROM vwDYPhoneList
|||

You need to execute the @.sql.

Do this at the end.

.....

DECLARE

@.sqlASnvarchar(MAX)

SET

@.sql= N'CREATE VIEW vwDYPhoneList AS SELECT * FROM (SELECT ClientID, PhoneType, PhoneNumber FROM tblPhones) as t

PIVOT (min(PhoneNumber) FOR PhoneType IN('

+ @.cols+ N')) AS pvt'EXECsp_executesql @.sql

SELECT

*FROM vwDYPhoneList

Access crosstab -> SQL crosstab

Hi all
I'm transferring some Access queries to SQL server and the crosstabs don't want to work, can anyone shed any light on the query below.

Thanks.

TRANSFORM Min(tCompany.cCompanyName) AS CompanyName
SELECT tProjContacts.ProjectID
FROM tCompany INNER JOIN (tProjContacts INNER JOIN tCompanyType ON tProjContacts.CoTypeId = tCompanyType.CoTypeId) ON tCompany.CompanyID = tProjContacts.CompanyID
WHERE (((tCompanyType.CoType) Like "*topo*" Or (tCompanyType.CoType) Like "*ground*"))
GROUP BY tProjContacts.ProjectID
PIVOT tCompanyType.CoType;SQL Server 2000 does not have built-in crosstab functionality. Crosstab queries can be constructed using the CASE statement, if you follow the example you will find under CROSSTAB in Books Online. This will now produce dynamic crosstabs as in Access, however, as you must predefine your column names/groups.
In general, crosstabs should not be performed on the back-end server anyway. Nine times out of ten crosstabbing is a presentation issue, and is best handled by your interface or reporting application. The main reason Access has this built in is that Access incorporates an interface and reporting engine utility with the database. Interfaces and reports like to have consistent recordset formats for input. Try creating an Access report off of a dynamic Access crosstab and you will see the problems that crop up when you let the database do the cross-tabbing.

Tuesday, March 6, 2012

About the value of each week

I have an rpt in crosstab that view the total sales of each salesman in every week. This is the rpt looks like:

Week 35 Week 36 Week 37 Week 38
Eric 100 200.25 300 500
Vlad 320 400 0 400

My problem is, if a salesman sales in a week is equal to zero, the sales that must be viewed will be the sale last week.

Here's the rpt must look like this..

Week 35 Week 36 Week 37 Week 38
Eric 100 200.25 300 500
Vlad 320 400 340 400use a formula for displaying value

something like

if sales>0 then
sales
else
previous(sales)|||Thanx! i will try

About the value of each week

I have an rpt that view the total sales of every week in crosstab design
Week 35 Week 36 Week 37 Week 38Do you have any specific question?