reading columns

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  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

• BRP

  Default port

  Points: 1433

  More actions

  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

• BRP

  Default port

  Points: 1433

  More actions

  October 25, 2003 at 4:55 am

  #479593

  ...oops

  UNION ALL operator