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
singhchAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
CORRECTION:
LEFT OUTER JOIN [Table B] b ON maxXandY.code = b.code AND
    ((whichCol = 'x' AND maxXandY.x = b.x) OR (whichCol = 'y' AND maxXandY.y = b.y))
0
 
LowfatspreadCommented:
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')
                                       )
                             )
0
 
LowfatspreadCommented:
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 ...
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
twoboatsCommented:
"Best practice for iterating rows"

Don't do it :)
0
 
LowfatspreadCommented:
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...

   
0
 
Scott PletcherSenior DBACommented:
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
0
 
Scott PletcherSenior DBACommented:
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.

0
 
Scott PletcherSenior DBACommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.