December 21, 2007 at 1:36 pm
Hi all,
In my SQL Server Management Studio Express, I executed the following code:
--scPivot1.sql--
USE ChemAveRpd
GO
IF EXISTS (SELECT PivotedLabTests FROM sys.tables
WHERE PivotedLabTests = 'pvt')
DROP TABLE pvt
GO
SELECT AnaltyeName, [1] AS 'Primary', [2] AS 'Duplicate', [3] 'QA'
[4] AS 'SampleName', [5] AS 'ProjectID'
INTO pvt
(SELECT LT.AnalyteID, S.SampleID
FROM dbo.LabTests LT
JOIN dbo.Samples S
ON LT.AnalyteID=S.SampleID) st
PIVOT
(
Result (LabTests)
FOR AnalyteName IN
([1], [2], [3], [4], [5])
) AS spvt
ORDER BY AnalyteName
GO
=================================
I got the following error messages:
Msg 207, Level 16, State 1, Line 2
Invalid column name 'PivotedLabTests'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'PivotedLabTests'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '4'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'st'.
Please help and tell me what wrong with my coding and how to correct the problems.
Thanks in advance,
Scott Chaqng
December 21, 2007 at 2:20 pm
Scott H. Chang (12/21/2007)
Hi all,IF EXISTS (SELECT PivotedLabTests FROM sys.tables
WHERE PivotedLabTests = 'pvt')
DROP TABLE pvt
Firstly, this. PivotedLabTests is not a column in sys.tables. I assume you're trying to check for the existence of a table and, if it exists, drop it. If so...
IF EXISTS (SELECT name FROM sys.tables
WHERE name = 'pvt')
DROP TABLE pvt
SELECT AnaltyeName, [1] AS 'Primary', [2] AS 'Duplicate', [3] 'QA'
[4] AS 'SampleName', [5] AS 'ProjectID'
You're missing a comma after [3] 'QA'
With those typos fixed, is it still giving a syntax error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply