asked on

Best practice for iterating rows

Hi there,

what would be the best way to achieve the following?

TABLE A
code x y
a 0 1
b 0 0
a 1 1
b 1 1
c 1 0

desired result is

TABLE B
code type
a x
a y
b x
b y
c x

The logic is .....
row 1 in table A we create a row in table B where the code is a and type is 'x' since such combination doesnt exist in table B
row 2 in table A, we dont create a row in table B becoz both col x and y in table A is zero
row 3 in table A, we only create a row in table B for code = a and type = y. we dont create another row for the column x becoz that already exists in table B
row 4 in table A, we create 2 rows in table B for code = b and type = x, and code = b and type = y
row 5 in table A, we create 1 row in table B for code = c and type = x

Is using cursor for the only way to achieve this? or is this a better approach? thanks

Lowfatspread

you don't need a cursor...

insert into tableb (code,type)
select code,case n when 1 then 'X' else 'Y' end
from tableA as A
cross join (select 1 as N union select 2 ) as x
Where ( (n=1 and x<> 0)
or (n=2 and y <> 0 )
)
and not exists (select code from tableb as y
where y.code = a.code
and ( (x.n = 1 and y.type = 'x')
or (x.n=2 and y.type='y')
)
)

Lowfatspread

ps if you really want the first row
when x=0 then you would add the condition as a separate insert to cover the condition ...

twoboats

"Best practice for iterating rows"

Don't do it :)

Lowfatspread

yeah sorry...

best practice (sql server)

avoid cursor processing , try and always do set based processing...
if you need to do row based iterations consider ( a temporary table, with an identity columns)
a while loop...

Scott Pletcher

Agree entirely with avoiding cursors. But I don't like the use of EXISTS() here because:
1) I don't think it will work, since the statement doing the INSERTed hasn't completed yet
2) It's less efficient than a method based on GROUPing

For example:

INSERT INTO [Table B]
SELECT code,
CASE WHEN whichCol = 'x' AND x > 0 THEN x
WHEN whichCol = 'y' AND y > 0 THEN y END AS type
FROM (
SELECT code, MAX(x) AS x, MAX(y) AS y
FROM [Table A]
GROUP BY code
HAVING MAX(x) > 0 OR MAX(y) > 0
) AS maxXandY
CROSS JOIN (
SELECT 'x' AS whichCol UNION ALL SELECT 'y'
) AS inlineData

Scott Pletcher

D'oh: Of course, if it [Table B] is an existing table, and not just being initially loaded, you will have to check it. Code to follow ASAP.

Scott Pletcher

INSERT INTO [Table B]
SELECT maxXandY.code,
CASE WHEN whichCol = 'x' AND maxXandY.x > 0 THEN maxXandY.x
WHEN whichCol = 'y' AND maxXandY.y > 0 THEN maxXandY.y END AS type
FROM (
SELECT code, MAX(x) AS x, MAX(y) AS y
FROM [Table A] a
GROUP BY code
HAVING MAX(x) > 0 OR MAX(y) > 0
) AS maxXandY
CROSS JOIN (
SELECT 'x' AS whichCol UNION ALL SELECT 'y'
) AS inlineData
LEFT OUTER JOIN [Table B] b ON maxXandY.code = b.code AND
((whichCol = 'x' AND maxXandY.x > 0) OR (whichCol = 'y' AND maxXandY.y > 0))
WHERE b.code IS NULL

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial