UDF or Stored Procedure able to split rows into multiple?

jercole · #1 (**permalink**) 09-29-08, 12:23

Hi,

I have a question regarding the functionality of UDFs/Stored Procedures and which one, if either, would be suitable to something I have to do.

I have a table with rows in it that contain 3 columns and an id. First column is a comma separated list of names. Next column is a comma separated list of lengths. Next column is a list of bits.

Basically, these columns all relate to each other. The list of names and lengths are used to determine the separation between the bits.

Is it possible to separate a single row and values within a column into multiple rows that contain name/value pairs?

In other words, In a single row:

ID:500
Names: Joe,Al,Jason
Lengths: 5, 3, 7
Bits: 110101111001111

Would end up becoming:

500 Joe 11010
500 Al 111
500 Jason 1001111

So basically I just need to know if either an SQL Procedure or a UDF would be more useful for splitting up a row into multiple rows while at the same time, performing operations on specific columns.

ARWinner · #2 (**permalink**) 09-29-08, 12:40

You could do it in a stored procedure. You might be able to do it with a UDF if you use recursive SQL.

Andy

jercole · #3 (**permalink**) 09-29-08, 13:57

Thanks Andy,

Is there something specific about stored procedures that makes this possible? Like a specific command?

I'm kind new to either of these things.

ARWinner · #4 (**permalink**) 09-29-08, 15:04

What you are going to have to do is create a global temporary table to put the rows in while you are parsing the outer data. This is easier to do in a SP.

Andy

jercole · #5 (**permalink**) 09-29-08, 15:15

Thanks for the info.

Peter.Vanroose · #6 (**permalink**) 09-29-08, 17:03

Quote:

Originally Posted by jercole

ID:500
Names: Joe,Al,Jason
Lengths: 5, 3, 7
Bits: 110101111001111

Would end up becoming:

500 Joe 11010
500 Al 111
500 Jason 1001111

The following does not need a stored proc; it just uses a recursive CTE:

Code:

WITH a(id,name,rname,pname,length,rlength,plength,bits,sbits,rbits,c) AS
( SELECT id, NULLIF('',''), names, locate(',',names), NULLIF('999','999'),
         lengths||',', locate(',',lengths), NULLIF('',''), 1, bits, 0 FROM Tbl
 UNION ALL
  SELECT id,
         CASE pname WHEN 0 THEN rname ELSE substr(rname, 1, pname-1) END,
         CASE pname WHEN 0 THEN NULLIF('','') ELSE substr(rname, pname+1) END,
         locate(',',substr(rname, pname+1)),
         CASE plength WHEN 0 THEN rlength ELSE substr(rlength, 1, plength-1) END,
         CASE plength WHEN 0 THEN NULLIF('','') ELSE substr(rlength,plength+1) END,
         locate(',',substr(rlength, plength+1)),
         CASE plength WHEN 0 THEN rbits
          ELSE substr(rbits, sbits, int(substr(rlength,1,plength-1))) END,
         sbits+
         CASE plength WHEN 0 THEN 0 ELSE int(substr(rlength,1,plength-1)) END,
         rbits,
         c+1
  FROM a WHERE c < 100 AND rname IS NOT NULL
)
SELECT id, name, bits FROM a
WHERE  c > 0

jercole · #7 (**permalink**) 09-29-08, 23:17

Thanks. I was looking at recursive SQL earlier and trying to come up with something like this. I'll test it out in the morning. Thanks!

Internet Services

Web Development

Digital Marketing

Consumer Tech