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