Using the Split Function from FAQ

[MrsMope987]

Hello,
I've read the FAQ on this site for the Split function, I want to take a field that has 13 different values delimited by a semi-colon. These values are for each month and one for adjustments. I need to have this split out as such:

Field Currently: 
RowID     Title
3         MANAGEMENT INCENTIVE EXP 
PeriodAmt
1500;1500;1500;1500;7500;1500;1500;1500;0;0;0;0;0

[RED]I need:
RowID Title       Jan   Feb   Mar   Apr   May   June  etc.
3     Management  1500  1500  1500  1500  7500  1500  etc.
[/red]

How do I do this? I was trying to use:

   DECLARE @PeriodAmt Varchar(20)
   SET @PeriodAmt = PeriodAmt
   SELECT RowID, PeriodAmt
      FROM GL09Data G
         INNER JOIN dbo.Split(@PeriodAmt,';') S
         ON G.RowID = S.Value  -- Implicit conversion to int
      ORDER BY S.TokenID

But I don't know how to reference the field correctly because I keep getting an error saying

[red]Msg 207, Level 16, State 1, Line 2
Invalid column name 'PeriodAmt'.[/red]

 

[jbenson001]

I belive you need to remove this line:
SET @PeriodAmt = PeriodAmt

And change this line:
INNER JOIN dbo.Split(@PeriodAmt,';') S
TO this:
INNER JOIN dbo.Split(PeriodAmt,';') S

 

[MrsMope987]

I made the changes you suggested, but I still get the same error.

 

[bborissov]

Try this:

SELECT RowID, PeriodAmt
      FROM GL09Data G
INNER JOIN dbo.Split(G.PeriodAmt,';') S
      ON G.RowID = S.Value  -- Implicit conversion to int
ORDER BY S.TokenID

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[MrsMope987]

Error Message (a different one though)
[red]Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "G.PeriodAmt" could not be bound.[/red]

 

[ZenRaven]

Besides the fact that PeriodAmt looks like it's not even a column in the referenced table, why would you try to join G.RowID to S.Value? They're not even the same piece of data.

 

[MrsMope987]

PeriodAmt is a column in the referenced table and I'm assuming by reading the code listed in the FAQ I copied this piece from that the S.Value is the identifier that matches the G.RowID.

 

[ZenRaven]

I'm nowhere near a SQL machine so here's an extremely ugly piece of code to try. it may or may not actually work and/or do what you want

DECLARE @i int, @periodamt varchar(8000)

declare @temp table (rowid int, title varchar(100), jan int, feb int, mar int, apr int, may int, jun int, jul int, aug int, sep int, oct int, nov int, dec int, adj int)

insert into @temp (rowid, title)
select rowid, title
from gl09data

select @i = max(rowid)
from @temp

while @i > 0
begin

select @periodamt = periodamt
from gl09data
where rowid = @i

insert into @temp2
select tokenid, value
from dbo.split(@periodAmt,';')



update @temp
set jan = case tokenid when 1 then value end,
feb= case tokenid when 2 then value end,
mar= case tokenid when 3 then value end,
apr= case tokenid when 4 then value end,
may= case tokenid when 5 then value end,
jun= case tokenid when 6 then value end,
jul= case tokenid when 7 then value end,
aug= case tokenid when 8 then value end,
sep= case tokenid when 9 then value end,
oct= case tokenid when 10 then value end,
nov= case tokenid when 11 then value end,
dec= case tokenid when 12 then value end,
adj= case tokenid when 13 then value end
from @temp2

delete @temp2

set @i = @i - 1
end

select *
from @temp

 

[gmmastros]

Take a look at this: thread183-1451986

It's remarkably similar to this question.

Also, why on earth would you want to store 13 pieces of data in the same column. That's a performance killer. Storing the data in 13 separate columns in the same table is slightly better, but still wrong. I encourage you to read up on [google]Database Normalization[/google]. When you have a properly normalized database, your queries usually execute faster and are certainly a lot easier to understand.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MrsMope987]

This isn't stored in a SQL Server, it's in a progress database. So I'm trying to pull it out of the progress database and put it into a SQL database in the correct format (one column for each value). I don't know WHY the progress one is set up that way, but there are a lot of tables like that. I'll try to work with the code you gave me ZenRaven and I'll report back

 

[ZenRaven]

Still ugly, but at least this time it works. I would actually suggest using DTS or SSIS for this as that is what they were designed for in the first place.

DECLARE @i INT,
    @x INT,
    @periodamt VARCHAR(8000)

DECLARE @temp TABLE
    (
      rowid INT,
      title VARCHAR(100),
      jan INT,
      feb INT,
      mar INT,
      apr INT,
      may INT,
      jun INT,
      jul INT,
      aug INT,
      sep INT,
      oct INT,
      nov INT,
      dec INT,
      adj INT
    )

DECLARE @temp2 TABLE ( tokenid INT, value INT )

INSERT  INTO @temp ( rowid, title )
        SELECT  rowid,
                title
        FROM    gl09data

SELECT  @i = MAX(rowid)
FROM    @temp

WHILE @i > 0
    BEGIN

        SELECT  @periodamt = periodamt
        FROM    gl09data
        WHERE   rowid = @i

        INSERT  INTO @temp2
                SELECT  tokenid,
                        value
                FROM    dbo.split(@periodAmt, ';')


        SET @x = 13
        WHILE @x > 0
            BEGIN
                UPDATE  @temp
                SET     jan = CASE tokenid
                                WHEN 1 THEN value
                                ELSE jan
                              END,
                        feb = CASE tokenid
                                WHEN 2 THEN value
                                ELSE feb
                              END,
                        mar = CASE tokenid
                                WHEN 3 THEN value
                                ELSE mar
                              END,
                        apr = CASE tokenid
                                WHEN 4 THEN value
                                ELSE apr
                              END,
                        may = CASE tokenid
                                WHEN 5 THEN value
                                ELSE may
                              END,
                        jun = CASE tokenid
                                WHEN 6 THEN value
                                ELSE jun
                              END,
                        jul = CASE tokenid
                                WHEN 7 THEN value
                                ELSE jul
                              END,
                        aug = CASE tokenid
                                WHEN 8 THEN value
                                ELSE aug
                              END,
                        sep = CASE tokenid
                                WHEN 9 THEN value
                                ELSE sep
                              END,
                        oct = CASE tokenid
                                WHEN 10 THEN value
                                ELSE oct
                              END,
                        nov = CASE tokenid
                                WHEN 11 THEN value
                                ELSE nov
                              END,
                        dec = CASE tokenid
                                WHEN 12 THEN value
                                ELSE dec
                              END,
                        adj = CASE tokenid
                                WHEN 13 THEN value
                                ELSE adj
                              END
                FROM    @temp,
                        @temp2
                WHERE   rowid = @i
                        AND tokenid = @x
        
                SET @x = @x - 1
            END

        DELETE  @temp2

        SET @i = @i - 1
    END

SELECT  *
FROM    @temp