Post reply

reading columns

  • October 24, 2003 at 12:35 am

    #82815

    Hi

    How can I loop through columns in a record to find any value that's not null? I think I'll be using a cursor?

    here's the problem:

    I have one recordset of values:

    id, label_ID, text1, text2, text3, num1, num2

    1, 80, null, Hey,null,null,5

    another table of labels for each of the above values:

    labelID,text1label, text2label,text3label, num1label, num2label

    80,null,theName,null,null,HowMany

    I have to find which column has values then find the same column in the second table to return the value which is the name of the first column.

    The result should look like this:

    id, Description, Value

    1,theName,Hey

    1,HowMany,5

    There's 42 columns to search.

    I'd be happy if I could find out how to scroll through each column in a recordset. If anybody can point me in the right direction for a better solution that would be great.

    Thanks in advance.

    Bruce

  • October 24, 2003 at 6:44 am

    #479445

    Normalize your schema. Here's a solution that normalizes your data so that the SQL is easy:

    

    SELECT v.Id, l.LabelName, v.Val

    FROM 

    (SELECT Id, Label_Id, CAST(Text1 as sql_variant) Val, 'Text1' Label FROM Vals

     UNION ALL

     SELECT Id, Label_Id, Text2, 'Text2' FROM Vals

     UNION ALL

     SELECT Id, Label_Id, Text3, 'Text3' FROM Vals

     UNION ALL

     SELECT Id, Label_Id, Num1, 'Num1' FROM Vals

     UNION ALL

     SELECT Id, Label_Id, Num2, 'Num2' FROM Vals) v

    JOIN

    (SELECT LabelId, Text1Label LabelName, 'Text1' Label FROM Labels

     UNION ALL

     SELECT LabelId, Text2Label, 'Text2' FROM Labels

     UNION ALL

     SELECT LabelId, Text3Label, 'Text3' FROM Labels

     UNION ALL

     SELECT LabelId, Num1Label, 'Num1' FROM Labels

     UNION ALL

     SELECT LabelId, Num2Label, 'Num2' FROM Labels) l ON v.Label_Id = l.LabelId AND v.Label = l.Label

    WHERE LabelName IS NOT NULL AND Val IS NOT NULL

    If it's not obvious from this code how you will now redesign your schema, then either educate yourself or hire someone with database knowledge. You're headed in a very wrong direction now.

    --Jonathan



    --Jonathan

  • October 25, 2003 at 4:46 am

    #479592

    thanks

    I have designed my db and have been given this other thing to get the data from. I knew there must be a better solution, it's good. I've never seen the JOIN ALL syntax before.

    I'm checking this out now

    thanks again

    Bruce

  • October 25, 2003 at 4:55 am

    #479593

    ...oops

    UNION ALL operator

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply