Inline table function help

20 Jan 2006 3:42 PM

....

Hi

I have a table looks something like this

CREATE TABLE TEST
(
        theid        INT,
        thevalue   TEXT
)

Example Data
        theid        thevalue
        1            1000,2000,4000,7000
        4            8000,2000,38383,333

From this table I want to create an extract which will look like this

    1,1000
    1,2000
    1,4000
    1,7000
    4,8000        (etc, hopefully you get where I'm going with this)

I have a lot of data so want to avoid cursors.

I've created an INLINE TABLE function to parse thru the string and return
each of the comma seperated values seperatly, and it works fine,for example

        SELECT * FROM dbo.fn_split ('1,2,3,4')
   returns
        1
        2
        3
        4

What I can't figure out is how I can use this function against my TEST
table, for example I want to do something like this

SELECT * FROM TEST, dbo.fn_split(test.thevalue) but of course this doesn't
work.....

Hopefully I've explained this well enough, any way of doing this? By the
way the column doesn't need to be a TEXT column if that makes my problem any
easier, the longest value is only 500 characters so could be a varchar.

Thanks in advance
Paul

20 Jan 2006 4:16 PM

ML

In SQL 2005 you can use CROSS APPLY, in SQL 2000 you'll have to normalize the
values row-by-row and insert the 1NF compliant data in a new table before any
such operations.

ML

---
http://milambda.blogspot.com/

20 Jan 2006 4:23 PM

Mark Williams

Give this a try

SELECT t1.theid, t2.[ColumnName] FROM [test] t1
INNER JOIN
(
SELECT a.theid, b.[ColumnName]
FROM [test] a, dbo.fn_split(a.thevalue)
WHERE a.theid = t1.theid
) t2
ON t1.theid = t2.theid

The derived table in the select statement takes advantage of the result of a
cartestion product to producte the results you want.

--

Show quote

"...." wrote:

> Hi
>
> I have a table looks something like this
>
> CREATE TABLE TEST
> (
>         theid        INT,
>         thevalue   TEXT
> )
>
> Example Data
>         theid        thevalue
>         1            1000,2000,4000,7000
>         4            8000,2000,38383,333
>
> From this table I want to create an extract which will look like this
>
>     1,1000
>     1,2000
>     1,4000
>     1,7000
>     4,8000        (etc, hopefully you get where I'm going with this)
>
> I have a lot of data so want to avoid cursors.
>
> I've created an INLINE TABLE function to parse thru the string and return
> each of the comma seperated values seperatly, and it works fine,for example
>
>         SELECT * FROM dbo.fn_split ('1,2,3,4')
>    returns
>         1
>         2
>         3
>         4
>
> What I can't figure out is how I can use this function against my TEST
> table, for example I want to do something like this
>
> SELECT * FROM TEST, dbo.fn_split(test.thevalue) but of course this doesn't
> work.....
>
> Hopefully I've explained this well enough, any way of doing this? By the
> way the column doesn't need to be a TEXT column if that makes my problem any
> easier, the longest value is only 500 characters so could be a varchar.
>
> Thanks in advance
> Paul
>
>
>
>
>
>
>
>
>
>
>
>

20 Jan 2006 4:25 PM

Anith Sen

Since fn_split() is an inline table-valued UDF, he won't be able to pass a
column name as a parameter.

--
Anith

20 Jan 2006 4:24 PM

Anith Sen

First of all, never have multiple values in a single column in a table -- it
is a fundamental flaw. If you are somehow left with such a schema, make it a
priority to redesign the schema, and represent one value per column in a
row. If such a redesign is not an option ( like 3rd party tables etc. ),
consider using a view for logical independence.

In any case, you can get this cleaned up using a table of sequentially
incrementing numbers. Here is an approach using a CTE ( a SQL 2005
feature ). If you are using SQL 2000 you can use:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp

; WITH Nbrs ( n ) AS (
    SELECT 1 UNION ALL SELECT n + 1 FROM Nbrs
     WHERE n <= COL_LENGTH( 'test', 'thevalue' )
) SELECT theid,
         SUBSTRING( thevalue, n, CHARINDEX( ',', thevalue + ',', n ) - n )
    FROM Nbrs
   INNER JOIN test
      ON SUBSTRING( ',' + thevalue, n, 1 ) = ','
     AND n < LEN( thevalue ) + 1
   ORDER BY theid,
         n + 1 - LEN( REPLACE( LEFT( thevalue, n ), ',', '' ) )
OPTION ( MAXRECURSION 32767 ) ;

The WITH and OPTION clause are SQL 2005 specific used to generate the
numbers recursively. They are not needed if Nbrs is used a base table.

--
Anith

20 Jan 2006 4:33 PM

....

I'm migrating out of that system into our new system, so I have the flaw
covered.

I'll give what you suggest a try, unfortunatly we have plans to upgrade to
2K5 later this year, so for now I'm stuck with 2K

Show quote

"Anith Sen" <an***@bizdatasolutions.com> wrote in message
news:uF2083dHGHA.1312@TK2MSFTNGP09.phx.gbl...
> First of all, never have multiple values in a single column in a table --
> it is a fundamental flaw. If you are somehow left with such a schema, make
> it a priority to redesign the schema, and represent one value per column
> in a row. If such a redesign is not an option ( like 3rd party tables
> etc. ), consider using a view for logical independence.
>
> In any case, you can get this cleaned up using a table of sequentially
> incrementing numbers. Here is an approach using a CTE ( a SQL 2005
> feature ). If you are using SQL 2000 you can use:
> http://www.bizdatasolutions.com/tsql/tblnumbers.asp
>
> ; WITH Nbrs ( n ) AS (
>    SELECT 1 UNION ALL SELECT n + 1 FROM Nbrs
>     WHERE n <= COL_LENGTH( 'test', 'thevalue' )
> ) SELECT theid,
>         SUBSTRING( thevalue, n, CHARINDEX( ',', thevalue + ',', n ) - n )
>    FROM Nbrs
>   INNER JOIN test
>      ON SUBSTRING( ',' + thevalue, n, 1 ) = ','
>     AND n < LEN( thevalue ) + 1
>   ORDER BY theid,
>         n + 1 - LEN( REPLACE( LEFT( thevalue, n ), ',', '' ) )
> OPTION ( MAXRECURSION 32767 ) ;
>
> The WITH and OPTION clause are SQL 2005 specific used to generate the
> numbers recursively. They are not needed if Nbrs is used a base table.
>
> --
> Anith
>

20 Jan 2006 4:46 PM

Alexander Kuznetsov

drop table #t
go
create table #t(id int, clist varchar(10))
--create table #n(n int)
insert into #t values(1, '1,2,34,5')
insert into #t values(2, '21,3,51,7')
go
select 1 n
into #n
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11
union all select 12
union all select 13
go
select t.id, substring(clist, n1, n2-n1-1)
from #t t join(
select t1.id, t1.n n1, min(t2.n) n2 from
(select id, n from #t join #n on substring(','+clist+',', n, 1)=',') t1

join
(select id, n from #t join #n on substring(','+clist+',', n, 1)=',') t2

on t1.id=t2.id
where t1.n<t2.n
group by t1.id, t1.n) t1
on t.id=t1.id
order by t.id,t1.n1

id
----------- ----------
1           1
1           2
1           34
1           5
2           21
2           3
2           51
2           7

(8 row(s) affected)