March 28, 2008 at 3:40 am
ISSUE
I have 98 row of data with several columns of data
1 row called companies has a company number
the next 3 column fields are memo fields which contain strings of data delimited by a comma - for example,,
companies agencies bus trad
1234 4654,344,1243 p1,r4,w3 143,654,777
What I need is by using the company number is to create a new row using the 1st agency number and again a 2nd row using the next agency number and so on...
then using the agency number do the exact again attaching the bus codes to each agency and finally doing the same again attaching the trad against the agency numbers.
any help on this would be fantastic or guidence on a better method
Thanks in advance
March 28, 2008 at 3:54 am
At first glance I can't think of a way of doing this without a cursor. But essentially you'd want to cursor through each row and then split the agencies up. Seeing as there are a small number of rows this shouldn't be a problem.
Something like this perhaps:
declare @company int
declare @agencies varchar(300)
declare @bus varchar(300)
declare @trad varchar(300)
declare my_cursor cursor
for
select companies, agencies, bus, trad
from my_table
open my_cursor
fetch next from my_cursor into @company, @agencies, @bus, @trad
while @@fetch_status = 0
begin
declare @agency int
--split the agencies
while charindex(',', @agencies) > 0
begin
set @agency = substring(@agencies,1,charindex(',', @agencies) - 1)
--you've now got an agency so do whatever you want with it (i.e. insert into another table)
set @agencies = substring(@agencies, charindex(',', @agencies) + 1, len(@agencies))
end
set @agency = @agencies --need to remember to fetch the last agency value from the list of agencies
--you've now got the last agency so do whatever you want with it (i.e. insert into another table)
fetch next from my_cursor into @company, @agencies, @bus, @trad
end
close my_cursor
deallocate my_cursor
I've only done the agencies but the same principle applies to buses, which you can do inside the same cursor along with agencies.
Hope that helps.
March 28, 2008 at 5:06 am
hopes it will give you an idea.
http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows
Thanks,
MH-09-AM-8694
March 28, 2008 at 5:38 am
Thanks Karl -what a quick and very impressive response
The code works great - 99% complete
Apologies as fairly new to SQL - how do I create a new table with the new data rows including all other columns from the table used ie.
the table with the data is called dbo.GOLD_0308 which contains the 98 rows of data
how do create a new table using the code you kindly provided with all the columnns added to each new row created for each agency.
Thanks again in advance for you help so far
Ian
March 28, 2008 at 6:03 am
ian_dunlop2000 (3/28/2008)
Thanks Karl -what a quick and very impressive responseThe code works great - 99% complete
Apologies as fairly new to SQL - how do I create a new table with the new data rows including all other columns from the table used ie.
the table with the data is called dbo.GOLD_0308 which contains the 98 rows of data
how do create a new table using the code you kindly provided with all the columnns added to each new row created for each agency.
Thanks again in advance for you help so far
Ian
Ian,
I have a question. How do you want the data to look? Is it like this:
companiesagencybustrad
12344654p1143
1234344r4654
1234143w3777
or:
companiesagencybustrad
12344654p1143
12344654r4143
12344654w3143
12344654p1654
Neither of the two options make a difference to the table structure. So I'd go for integer for company, agency and trad, and then char or varchar for bus.
March 28, 2008 at 6:15 am
HI Karl
The data fields are
Company - int
Agency - nvarchar
bus lines - nvarchar
trad seg - nvarchar
The rows to be created for example,,
company number 5667 has 7 agencies in the string to which would then in total have 8 rows of data for that company number
1 the original row with the first agency in the list and the other 6 agencies created in new rows below.
Or would it be more practical to create all the rows using the 7 agencies and then delete the used row once the new rows have been generated.
Thanks again for your help Karl
Regards
Ian
March 28, 2008 at 6:30 am
Hi Ian. I get it now.
So each company has 3 agencies, 3 buses and 3 trads. And the first agency is paired up with the first bus and the first trad, and so on.... is that right?
Assuming that's the case then you could do something like this. Note that I have create a new table called my_new_table and I'm inserting the newly formatted rows into this table. That would be my preferred choice for a couple of reasons. One, you can test this without affecting the main table, and two, you can then just delete all the data from the original table and re-populate it from the new table - once you're satisfied that the results are what you're expecting.
Also, the new table uses an integer for agency and trad, and a varchar(3) for bus. Change these (and the corresponding @agency, @bus and @trad parameters) accordingly.
/*
create table my_new_table(company int, agency int, bus varchar(3), trad int)
*/
declare @company int
declare @agencies varchar(300)
declare @buses varchar(300)
declare @trads varchar(300)
declare my_cursor cursor
for
select companies, agencies, buses, trads
from dbo.GOLD_0308
open my_cursor
fetch next from my_cursor into @company, @agencies, @buses, @trads
while @@fetch_status = 0
begin
declare @bus varchar(3)
declare @trad integer
declare @agency integer
--split the agencies
while charindex(',', @agencies) > 0
begin
set @agency = substring(@agencies,1,charindex(',', @agencies) - 1)
--now that we've got an agency, get a bus code
set @bus = substring(@buses,1,charindex(',', @buses) - 1)
--do the same for the trad
set @trad = substring(@trads,1,charindex(',', @trads) - 1)
insert into my_new_table(company, agency, bus, trad) values (@company, @agency, @bus, @trad)
set @trads = substring(@trads, charindex(',', @trads) + 1, len(@trads))
set @buses = substring(@buses, charindex(',', @buses) + 1, len(@buses))
set @agencies = substring(@agencies, charindex(',', @agencies) + 1, len(@agencies))
end
set @agency = @agencies
set @bus = @buses
set @trad = @trads
insert into my_new_table(company, agency, bus, trad) values (@company, @agency, @bus, @trad)
fetch next from my_cursor into @company, @agencies, @buses, @trads
end
close my_cursor
deallocate my_cursor
select * from my_new_table
--truncate the table to retest
--truncate table my_new_table
March 28, 2008 at 7:44 am
HI Karl - apoligies - feel its nearly complete - just my inexperience stepping in !!
The Company numbers are unique as is the agency numbers, they and bus , trad will vary in length - ie one company number may relate to 6 agency numbers and the next company number might have 12 agency numbers against it, this is the case also with the Bus and Trad fields.
So in essence 1 company number might generate 12 new rows based on the agency list in the meno field it checks and creates new rows for and the next company number might have 4 agency numbers etc.
I was thinking that if I could create a table with the new rows of agencies using the source table then repeat the code to add the bus and then the trad so that the final table would have everthing split out into rows in the new table - which will be many in the end.
the code I have created below --
Create table new_table(Companies int,Agency varchar(20),BUS VARCHAR(10),TRAD VARCHAR(10)
*/
DECLARE @Companies int
DECLARE @Agencies VARCHAR(100)
DECLARE @BUS VARCHAR(10)
DECLARE @TRAD VARCHAR(10)
DECLARE MY_CURSOR CURSOR
FOR
SELECT Companies,Agencies,Business_Lines,Trading_Segments
FROM dbo.GOLD_0308
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @Companies,@Agencies,@BUS,@TRAD
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @AGENCY VARCHAR(15)
DECLARE @TRAD2 VARCHAR(10)
DECLARE @BUS2 VARCHAR(10)
--SPLIT AGENCIES
WHILE CHARINDEX(',', @Agencies) > 0
BEGIN SET @AGENCY = SUBSTRING(@Agencies,1,CHARINDEX(',',@Agencies)-1)
SET @TRAD2 = SUBSTRING(@TRAD,1,CHARINDEX(',',@TRAD),-1)
SET @BUS2 = SUBSTRING(@BUS,1,CHARINDEX(',',@BUS)-1)
INSERT INTO New_Table(Companies,Agencies,BUS,TRAD) VALUES (@Companies,@AGENCY,@TRAD2,@BUS2)
SET @TRAD = SUBSTRING(@TRAD,CHARINDEX(',',@TRAD)+1,LEN(@TRAD))
SET @BUS = SUBSTRING(@BUS,CHARINDEX(',',@BUS)+1,LEN(@BUS))
SET @Agencies = SUBSTRING(@Agencies,CHARINDEX(',',@Agencies)+1, LEN(@Agencies))
END
SET @AGENCY = @Agencies
SET @TRAD2 = @TRAD
SET @BUS2 = @BUS
INSERT INTO new_Table (Companies,bus,trad) values (@Companies,Agencies,TRAD2,BUS2)
FETCH NEXT FROM MY_CURSOR INTO @Companies,@Agencies,@BUS,@TRAD
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
ERROR MESSAGE GENERATED…
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.
Msg 174, Level 15, State 1, Line 24
The substring function requires 3 argument(s).
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near ')'.
Msg 128, Level 15, State 1, Line 37
The name "Agencies" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Have gone through the code which did previously work but don't understand pass this what the errors are?
Again may thanks for all your assistance - would not be this close without your help
Regards
Ian
Suffolk - England
March 28, 2008 at 8:55 am
Ian,
The first error I think is related to the create table statement you have at the top. There's a missing closing bracket there.
The substring error relates to the substring just after you start the WHILE loop inside the cursor:
SET @TRAD2 = SUBSTRING(@TRAD,1,CHARINDEX(',',@TRAD),-1)
There's an extra comma just before the "-1".
The error that say "Agencies" is not permitted in this context is because you should have put in parameter names in the VALUES clause. Looks like you just forgot to put the @ sign infront of the names.
NSERT INTO new_Table (Companies,Agencies, bus,trad) values (@Companies,@Agencies,@TRAD2,@BUS2)
You also missed out Agencies in the INSERT INTO clause.
By the way, if you double click the error in management studio it will take you to the line on which the error occurred.
March 28, 2008 at 9:31 am
Thanks Karl for all your help - you have been a STAR:)
Its just about doing what I want - a little more tweeking and I will have what I want
Thanks again
Ian
March 28, 2008 at 9:33 am
No problem. Glad I could help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply