Solved

cannot create table from data

Posted on 2007-12-03
3
168 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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