Columns to table

Question

Columns to table

Bill Cresswell

Newbie

Points: 2
More actions
August 26, 2005 at 6:00 pm

#187235

I have a spreadsheet with Date1 Offer1 Receipt1 Date2 Offer2 Receipt2 ... all the way to Date8 Offer8 Receipt8. I would like to create a table listing Date Offer Receipt. I have tried SELECT Date1 & Offer1 & Receipt1 from XYZ UNION SELECT Date2 & Offer2 & Receipt2 from XYZ ... all the way through to UNION SELECT Date8 & Offer8 & Receipt8 from XYZ WHERE etc.
Have I got this approach all wrong? I'm a real newbie to all of this!

Cheers Bill

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Suggest you import the spreadsheet into SQL Server "as is" and then normalise. Your general approach should work though:

insert into tblNormalised(date, offer, receipt)

select date1, offer1, receipt1 from imported union

select date2, offer2, receipt2 from imported union

.... etc etc ........

You may need to put WHERE clauses against each of the SELECTs to avoid bringing in any blank records (unless all 8 repeats are always populated).

Bill Cresswell Newbie Points: 2 More actions · Answer 2

Thanks very much, realise now that I was using & instead of commas and had to "declare" name for table with its contents, silly me. I'm still learning.