Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009

    Unanswered: Need help on SQL query


    I need help modify following query:

    SELECT Distinct

    Exam.Code as Exam_Code,

    ExamResources.ResourceGroupCode as Res_Tdy,

    ExamChargeCode.ChargeCodeCode as BC


    Exam, ExamChargeCode, ExamResources

    WHERE Exam.ExamID = ExamChargeCode.ExamID

    AND ExamChargeCode.ExamID = ExamResources.ExamID

    Here's the result:

    Exam_Code Res_Tdy BC
    BBIDIG DIXR3 431108
    BBIDIG DIXR3 431235
    BBIDIG DIXR3 431108
    BBIDIG DIXR3 431235
    BDLOCL RGBIB1 431154
    BDLOCL RGBIB1 431157
    BDLOCL RGBIB1 431158
    BDLOCR RGBIB1 431149
    BDLOCR RGBIB1 431158
    BDLOCR RGBIB1 431170

    Now users want the result in the following format:

    Exam_Code Res_Tdy BC1 BC2 BC3 BC4 BC5

    BBIDIG DIXR3 431108 431235 431108 431235
    BDLOCL RGBIB1 431154 431157 431158
    BDLOCR RGBIB1 431149 431158 431170

    How to modify my query to achive the above result?

    Thank you!

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    So how are we supposed to know which records should fall in column 1, which in column 2, etc?

    The first thing you should learn about SQL is that it is not meant for this type of data massaging. This is a data display and formatting task, and should be handled by your application or reporting software.

    If you (or your users) insist on doing this with SQL, look up Pivot queries in SQL Server 2008, or Crosstab Queries in earlier versions.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts