SSMS Express: Using PIVOT operator to Create Pivot Table - Error Messages 156 & 207

Hot Topic

Calling a Web Service with data from a SQL Server table one row at a time
SQL Mgt Studio 2005 - Printing Diagrams
Help says ODBC is Available. Where?
Set amounts to zero in addition if no records found
SQL Express and selling as a hosting service.
Problem Rendering to PDF File
Inserting images
New to SQL 2000 Reporting Services - Need Some Help
TRANSACTION
Enterprise Or Express Edition?

SQL Server

display string having length greater than 255
Missing Elements in Report Manager
AdventureWorks db for 101 Samples
Re : Enabling / Disabling Data Flow Tasks
Data Sources and Connection Managers
SQL 2005 Management Studio Timeout expired
Practical Uses for the "Embedded Code" functionality
The connection manager 'OLEDB' is not properly installed on this computer
Passing rc:Toolbar=False in URL causes report to render incorrectly
Unable to create database from VS

SSMS Express: Using PIVOT operator to Create Pivot Table - Error Messages 156 & 207

Hi all,

In MyDatabase, I have a TABLE dbo.LabData created by the following SQLQuery.sql:

USE MyDatabase

GO

CREATE TABLE dbo.LabResults

(SampleID int PRIMARY KEY NOT NULL,

SampleName varchar(25) NOT NULL,

AnalyteName varchar(25) NOT NULL,

Concentration decimal(6.2) NULL)

GO

--Inserting data into a table

INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)

VALUES (1, 'MW2', 'Acetone', 1.00)

INSERT … ) VALUES (2, 'MW2', 'Dichloroethene', 1.00)

INSERT … ) VALUES (3, 'MW2', 'Trichloroethene', 20.00)

INSERT … ) VALUES (4, 'MW2', 'Chloroform', 1.00)

INSERT … ) VALUES (5, 'MW2', 'Methylene Chloride', 1.00)

INSERT … ) VALUES (6, 'MW6S', 'Acetone', 1.00)

INSERT … ) VALUES (7, 'MW6S', 'Dichloroethene', 1.00)

INSERT … ) VALUES (8, 'MW6S', 'Trichloroethene', 1.00)

INSERT … ) VALUES (9, 'MW6S', 'Chloroform', 1.00)

INSERT … ) VALUES (10, 'MW6S', 'Methylene Chloride', 1.00)

INSERT … ) VALUES (11, 'MW7', 'Acetone', 1.00)

INSERT … ) VALUES (12, 'MW7', 'Dichloroethene', 1.00)

INSERT … ) VALUES (13, 'MW7', 'Trichloroethene', 1.00)

INSERT … ) VALUES (14, 'MW7', 'Chloroform', 1.00)

INSERT … ) VALUES (15, 'MW7', 'Methylene Chloride', 1.00)

INSERT … ) VALUES (16, 'TripBlank', 'Acetone', 1.00)

INSERT … ) VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)

INSERT … ) VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)

INSERT … ) VALUES (19, 'TripBlank', 'Chloroform', 0.76)

INSERT … ) VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)

GO

A desired Pivot Table is like:

MW2 MW6S MW7 TripBlank

Acetone 1.00 1.00 1.00 1.00

Dichloroethene 1.00 1.00 1.00 1.00

Trichloroethene 20.00 1.00 1.00 1.00

Chloroform 1.00 1.00 1.00 0.76

Methylene Chloride 1.00 1.00 1.00 0.51

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I write the following SQLQuery.sql code for creating a Pivot Table from the Table dbo.LabData by using the PIVOT operator:

USE MyDatabase

GO

USE TABLE dbo.LabData

GO

SELECT AnalyteName, [1] AS MW2, Devil AS MW6S, [11] AS MW7, [16] AS TripBlank

FROM

(SELECT SampleName, AnalyteName, Concentration

FROM dbo.LabData) p

PIVOT

(

SUM (Concentration)

FOR AnalyteName IN ([1], Devil, [11], [16])

) AS pvt

ORDER BY SampleName

GO

////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I executed the above-mentioned code and I got the following error messages:

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'TABLE'.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'AnalyteName'.

I do not know what is wrong in the code statements of my SQLQuery.sql. Please help and advise me how to make it right and work for me.

Thanks in advance,

Scott Chang




Answer this question

SSMS Express: Using PIVOT operator to Create Pivot Table - Error Messages 156 & 207

  • New Man

    HI Scott,
     
    For your first question, I don't think there are any great solutions for free (i.e., for the Express Edition of SQL Server).  If you were to upgrade to Standard Edition ($5,000/processor, I believe), you would have access to SQL Server Integration Services, which is a data integration tool that you could use to programatically transform and load the data from all of the formats in which you receive it, into a standardized format.  If your input data is fairly consistent and there aren't a lot of complex transformations, I suppose you could accomplish the same thing by writing your own data importation tools, e.g. in a .NET language.  I'm not sure which option would be cheaper in the end -- I guess it depends on your hourly rate ;)
     
    As for your second question, pretty much the same answer -- for data not in SQL Server, converting to XML will require some kind of program that supports XML.  For data in SQL Server, you may be able to use some of the FOR XML enhancements to the SELECT statement to transform the data, or make use of the XML datatype.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     

    Hi Adam, Thank you very much for your valuable response and corrections on my code.

    The revised code works nicely like a charm!!!

    I am a chemist and I deal with lots of chemical data frequently. Currently, I get the electronic data in the extensions of .mdb, .xls or .csv from commercial chemical laboratories in USA and I feed into the Microsoft Access 2003/Excel 2003 based software programs for precessing. Now, the Express Edition and other editions of Visual Studio 2005 use the data with the ..mdf type of data. To input the .mdf type of data to me is very hard manually as I tried recently. I have wondered for quite a while about how to input the .mdf type of data electronically. I like to ask you the following 2 questions:

    (1) Is it possible to convert the electronic data with the extensions of .mdb, .xls or .cvs to the .mdf typed data

    (2) Can we convert the .mdb, .xls or .csv typed data to the XML typed data Can we convert the XML typed data to the .mdf typed data

    Please advise.

    Thanks again,

    Scott Chang


  • Keandre

    Adam,

    Is OPENROWSET supported in the Express version

    If it is then that would be a way to import access,text, excel and csv files without upgrading to SQL Server 2005 Standard Edition

    Denis the SQL Menace

    http://sqlservercode.blogspot.com/

  • Frank Palinkas

    No clue, but I bet it is, and you're right -- he could write scripts that use OPENROWSET... or, he could even just create linked servers to the data sources.  Lots of "by hand" options, I suppose.  But for those of us who are lazy, SSIS is a much quicker tool ;-)
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     

    Adam,

    Is OPENROWSET supported in the Express version

    If it is then that would be a way to import access,text, excel and csv files without upgrading to SQL Server 2005 Standard Edition

    Denis the SQL Menace

    http://sqlservercode.blogspot.com/

  • A fok

    First problem:
     
     

    USE TABLE dbo.LabData

    GO

    You can just remove that -- there is no USE TABLE syntax in T-SQL.

     

    Second, I think you need to re-write your pivot query to the following:

     

    SELECT AnalyteName, [MW2], [MW6S], [MW7], [TripBlank]

    FROM

    (SELECT SampleName, AnalyteName, Concentration

    FROM dbo.LabResults) p

    PIVOT

    (

    SUM (Concentration)

    FOR SampleName IN ([MW2], [MW6S], [MW7], [TripBlank])

    ) AS pvt

    ORDER BY AnalyteName

    GO

     


    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     

    Hi all,

    In MyDatabase, I have a TABLE dbo.LabData created by the following SQLQuery.sql:

    USE MyDatabase

    GO

    CREATE TABLE dbo.LabResults

    (SampleID int PRIMARY KEY NOT NULL,

    SampleName varchar(25) NOT NULL,

    AnalyteName varchar(25) NOT NULL,

    Concentration decimal(6.2) NULL)

    GO

    --Inserting data into a table

    INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)

    VALUES (1, 'MW2', 'Acetone', 1.00)

    INSERT … ) VALUES (2, 'MW2', 'Dichloroethene', 1.00)

    INSERT … ) VALUES (3, 'MW2', 'Trichloroethene', 20.00)

    INSERT … ) VALUES (4, 'MW2', 'Chloroform', 1.00)

    INSERT … ) VALUES (5, 'MW2', 'Methylene Chloride', 1.00)

    INSERT … ) VALUES (6, 'MW6S', 'Acetone', 1.00)

    INSERT … ) VALUES (7, 'MW6S', 'Dichloroethene', 1.00)

    INSERT … ) VALUES (8, 'MW6S', 'Trichloroethene', 1.00)

    INSERT … ) VALUES (9, 'MW6S', 'Chloroform', 1.00)

    INSERT … ) VALUES (10, 'MW6S', 'Methylene Chloride', 1.00)

    INSERT … ) VALUES (11, 'MW7', 'Acetone', 1.00)

    INSERT … ) VALUES (12, 'MW7', 'Dichloroethene', 1.00)

    INSERT … ) VALUES (13, 'MW7', 'Trichloroethene', 1.00)

    INSERT … ) VALUES (14, 'MW7', 'Chloroform', 1.00)

    INSERT … ) VALUES (15, 'MW7', 'Methylene Chloride', 1.00)

    INSERT … ) VALUES (16, 'TripBlank', 'Acetone', 1.00)

    INSERT … ) VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)

    INSERT … ) VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)

    INSERT … ) VALUES (19, 'TripBlank', 'Chloroform', 0.76)

    INSERT … ) VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)

    GO

    A desired Pivot Table is like:

    MW2 MW6S MW7 TripBlank

    Acetone 1.00 1.00 1.00 1.00

    Dichloroethene 1.00 1.00 1.00 1.00

    Trichloroethene 20.00 1.00 1.00 1.00

    Chloroform 1.00 1.00 1.00 0.76

    Methylene Chloride 1.00 1.00 1.00 0.51

    //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

    I write the following SQLQuery.sql code for creating a Pivot Table from the Table dbo.LabData by using the PIVOT operator:

    USE MyDatabase

    GO

    USE TABLE dbo.LabData

    GO

    SELECT AnalyteName, [1] AS MW2, [6] AS MW6S, [11] AS MW7, [16] AS TripBlank

    FROM

    (SELECT SampleName, AnalyteName, Concentration

    FROM dbo.LabData) p

    PIVOT

    (

    SUM (Concentration)

    FOR AnalyteName IN ([1], [6], [11], [16])

    ) AS pvt

    ORDER BY SampleName

    GO

    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////

    I executed the above-mentioned code and I got the following error messages:

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'TABLE'.

    Msg 207, Level 16, State 1, Line 1

    Invalid column name 'AnalyteName'.

    I do not know what is wrong in the code statements of my SQLQuery.sql. Please help and advise me how to make it right and work for me.

    Thanks in advance,

    Scott Chang

  • hivesoft

    Hi Adam, Thank you very much for your valuable response and corrections on my code.

    The revised code works nicely like a charm!!!

    I am a chemist and I deal with lots of chemical data frequently. Currently, I get the electronic data in the extensions of .mdb, .xls or .csv from commercial chemical laboratories in USA and I feed into the Microsoft Access 2003/Excel 2003 based software programs for precessing. Now, the Express Edition and other editions of Visual Studio 2005 use the data with the .mdf type of data. To input the .mdf type of data to me is very hard manually as I tried recently. I have wondered for quite a while about how to input the .mdf type of data electronically. I like to ask you the following 2 questions:

    (1) Is it possible to convert the electronic data with the extensions of .mdb, .xls or .cvs to the .mdf typed data

    (2) Can we convert the .mdb, .xls or .csv typed data to the XML typed data Can we convert the XML typed data to the .mdf typed data

    Please advise.

    Thanks again,

    Scott Chang



  • SSMS Express: Using PIVOT operator to Create Pivot Table - Error Messages 156 & 207
Answer this question