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 HomePhoneNumberFROM
(SELECT ClientID, PhoneType, PhoneNumberFROM tblPhones) pWHERE
PhoneTypeIN('home','office')GROUP
BY ClientID
--Or PIVOT solution:(SQL Server 2005)
SELECT
ClientID, office, homeFROM
(SELECT ClientID, PhoneType, PhoneNumberFROM tblPhones) pPIVOT
(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 tPIVOT (min(PhoneNumber) FOR PhoneType IN('
+ @.cols+ N')) AS pvt'EXECsp_executesql @.sql
SELECT
*FROM vwDYPhoneList
