?
Solved

Best practice for iterating rows

Posted on 2007-07-23
8
Medium Priority
?
197 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:singhch
  • 4
  • 3
8 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 19546639
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 19546647
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
 
LVL 14

Expert Comment

by:twoboats
ID: 19547007
"Best practice for iterating rows"

Don't do it :)
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 19547267
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19551231
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19551284
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19551318
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 19551329
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question