Solved

cannot create table from data

Posted on 2007-12-03
3
163 Views
Last Modified: 2010-03-20
I am having trouble creating a new table that is populated from the data from the query. help would be appreciated
regards in advance
select

		 vehicle_id ,

		 MAX(case when [schema_id]=101 then data_value else NULL end) AS UniqueId , 105 AS ReasonValue,

		 MAX(case when [schema_id]=105 then data_value else NULL end) AS ReasonCode,

		 MAX(case when [schema_id]=108 then data_value else NULL end) AS Yr, 110 AS CountryValue,

		 Max(case when [schema_id]=110 then data_value else NULL end) AS CountryCode,

		 MAX(case when [schema_id]=115 then data_value else NULL end) AS VersionIntro,

		 MAX(case when [schema_id]=125 then data_value else NULL end) AS IntroDate, 

                 MAX(case when [schema_id]=128 then data_value else NULL end) AS Make,

		 MAX(case when [schema_id]=129 then data_value else NULL end) AS Model,

		 MAX(case when [schema_id]=130 then data_value else NULL end) AS ModelDesc,

		 MAX(case when [schema_id]=131 then data_value else NULL end) AS Variant,

		 MAX(case when [schema_id]=302 then data_value else NULL end) AS Version, 

                 MAX(case when [schema_id]=602 then data_value else NULL end) AS Doors, 603 AS BodyValue,

		 MAX(case when [schema_id]=603 then data_value else NULL end) AS BodyCode, 606 AS RoofValue,

		 MAX(case when [schema_id]=606 then data_value else NULL end) AS RoofCode, 608 AS WheelbaseValue,

		 MAX(case when [schema_id]=608 then data_value else NULL end) AS WheelbasCode,

		 MAX(case when [schema_id]=702 then data_value else NULL end) AS Seats,

		 MAX(case when [schema_id]=6502 then data_value else NULL end) AS DriveType, 

                 MAX(case when [schema_id]=7402 then data_value else NULL end) AS CC,

		 MAX(case when [schema_id]=7407 then data_value else NULL end) AS Cyl,

		 MAX(case when [schema_id]=7408 then data_value else NULL end) AS EngineConfig, 7414 AS ValveGearValue,

		 MAX(case when [schema_id]=7414 then data_value else NULL end) AS ValveGearCode, 

                 MAX(case when [schema_id]=7417 then data_value else NULL end) AS Valves,

		 MAX(case when [schema_id]=7502 then data_value else NULL end) AS Compressor, 8702 AS FuelVlaue, 

                 MAX(case when [schema_id]=8702 then data_value else NULL end) AS FuelCode, 8703 AS AltFuelValue,

		 MAX(case when [schema_id]=8703 then data_value else NULL end) AS AltFuelCode, 

	         MAX(case when [schema_id]=20602 then data_value else NULL end) AS Trans,

		 MAX(case when [schema_id]=20603 then data_value else NULL end) AS Gears,

		 MAX(case when [schema_id]=15304 then data_value else NULL end) AS BHP,

		 MAX(case when [schema_id]=15303 then data_value else NULL end) AS Kw

FROM         Jato.dbo.equipment

GROUP BY vehicle_id, ReasonValue, CountryValue, BodyValue, RoofValue, WheelbaseValue, ValveGearValue, FuelVlaue, AltFuelValue

Open in new window

0
Comment
Question by:PeterBaileyUk
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 20394710
Eg:


Select Col1 , col2
INTO <NewTableName>
from someExisitingTable
0
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20394714
Try this and change the table name after into. I had given equipmentcounts as new tablename

select

		 vehicle_id ,

		 MAX(case when [schema_id]=101 then data_value else NULL end) AS UniqueId , 105 AS ReasonValue,

		 MAX(case when [schema_id]=105 then data_value else NULL end) AS ReasonCode,

		 MAX(case when [schema_id]=108 then data_value else NULL end) AS Yr, 110 AS CountryValue,

		 Max(case when [schema_id]=110 then data_value else NULL end) AS CountryCode,

		 MAX(case when [schema_id]=115 then data_value else NULL end) AS VersionIntro,

		 MAX(case when [schema_id]=125 then data_value else NULL end) AS IntroDate, 

                 MAX(case when [schema_id]=128 then data_value else NULL end) AS Make,

		 MAX(case when [schema_id]=129 then data_value else NULL end) AS Model,

		 MAX(case when [schema_id]=130 then data_value else NULL end) AS ModelDesc,

		 MAX(case when [schema_id]=131 then data_value else NULL end) AS Variant,

		 MAX(case when [schema_id]=302 then data_value else NULL end) AS Version, 

                 MAX(case when [schema_id]=602 then data_value else NULL end) AS Doors, 603 AS BodyValue,

		 MAX(case when [schema_id]=603 then data_value else NULL end) AS BodyCode, 606 AS RoofValue,

		 MAX(case when [schema_id]=606 then data_value else NULL end) AS RoofCode, 608 AS WheelbaseValue,

		 MAX(case when [schema_id]=608 then data_value else NULL end) AS WheelbasCode,

		 MAX(case when [schema_id]=702 then data_value else NULL end) AS Seats,

		 MAX(case when [schema_id]=6502 then data_value else NULL end) AS DriveType, 

                 MAX(case when [schema_id]=7402 then data_value else NULL end) AS CC,

		 MAX(case when [schema_id]=7407 then data_value else NULL end) AS Cyl,

		 MAX(case when [schema_id]=7408 then data_value else NULL end) AS EngineConfig, 7414 AS ValveGearValue,

		 MAX(case when [schema_id]=7414 then data_value else NULL end) AS ValveGearCode, 

                 MAX(case when [schema_id]=7417 then data_value else NULL end) AS Valves,

		 MAX(case when [schema_id]=7502 then data_value else NULL end) AS Compressor, 8702 AS FuelVlaue, 

                 MAX(case when [schema_id]=8702 then data_value else NULL end) AS FuelCode, 8703 AS AltFuelValue,

		 MAX(case when [schema_id]=8703 then data_value else NULL end) AS AltFuelCode, 

	         MAX(case when [schema_id]=20602 then data_value else NULL end) AS Trans,

		 MAX(case when [schema_id]=20603 then data_value else NULL end) AS Gears,

		 MAX(case when [schema_id]=15304 then data_value else NULL end) AS BHP,

		 MAX(case when [schema_id]=15303 then data_value else NULL end) AS Kw

FROM Jato.dbo.equipment

Into equipmentcounts

GROUP BY vehicle_id, ReasonValue, CountryValue, BodyValue, RoofValue, WheelbaseValue, ValveGearValue, FuelVlaue, AltFuelValue

 

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 20394801
it said incorrect syntax near the keyword into but we cannot see why
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now