June 23, 2005 at 9:16 am
This all may be a little over my head, but I'll throw it out there and see if anyone can help.
I have a need to do what I guess you would call a comparision on two columns in an excel spreadsheet and move the rows around to match up any duplicates. in other words, if 'Orlando' is found in cell A20 AND in B21, B35 and B40, move those rows so that all of the 'Orlando's end up in a grouping.
Is there a way to maybe there was a way to dump the excel into a SQL Server table, massage the data to match up the rows and then dump it back out to a spreadsheet (while preserving the rest of the data in the spreadsheet)???
June 23, 2005 at 11:26 am
This is one possible solution; I'm sure there is probably a better select statement.
I have assumed you have already pumped your Excel spreadsheet into SQL Server and called the table Cities. I have also assumed that the first column City does not have repeated values.
CREATE TABLE #Cities( City varchar(25),
SecondCity varchar(25),
OtherInformation varchar(35),
ExtraColumn integer)
INSERT INTO #Cities VALUES( 'Orlando', 'Cedar Rapids', 'Matches three other records', 1)
INSERT INTO #Cities VALUES( 'Baltimore', 'Orlando', 'Match 1', 2)
INSERT INTO #Cities VALUES( 'Cedar Rapids', 'Pittsburg', 'No Match to First Colum', 3)
INSERT INTO #Cities VALUES( 'Daytona', 'Leesburg', 'No Match to First Column', 4)
INSERT INTO #Cities VALUES( 'Washington DC', 'Orlando', 'Match 2', 5)
INSERT INTO #Cities VALUES( 'Hollywood', 'Townsville', 'No Match to First Column', 6)
INSERT INTO #Cities VALUES( 'Arlington', 'Pittsburg', 'No Match to First Column', 7)
INSERT INTO #Cities VALUES( 'Dubuque', 'Orlando', 'Match 3', 8)
SELECT #Cities.City, ISNULL( b.SecondCity, #Cities.SecondCity) AS SecondCity,
ISNULL( b.OtherInformation, #Cities.OtherInformation) AS OtherInformation,
ISNULL( b.ExtraColumn, #Cities.ExtraColumn) AS ExtraColumn
FROM #Cities
LEFT JOIN( SELECT SecondCity, OtherInformation, ExtraColumn
FROM #Cities) b ON( #Cities.City = b.SecondCity)
GROUP BY #Cities.City, b.SecondCity, #Cities.SecondCity, b.OtherInformation,
#Cities.OtherInformation, b.ExtraColumn, #Cities.ExtraColumn
DROP TABLE #Cities
I wasn't born stupid - I had to study.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply