How do I export fields from repeated segments?

Question:

I am trying to pull data from multiple HL7 messages using the SQL query: SELECT *, PID-5.1 as ‘B Last Name’, PID-5.2 as ‘B First Name’, OBR-4.1 as ‘Order Number’, OBR-4.2 as ‘Order’ WHERE MSH-11 != ‘T’

The issue I’m having is messages with multiple OBRs. How can I get data all the data from the SQL statement and the data from each OBR on each message?

Answer: 

Unfortunately you cannot use the HL7 SQL tool for this. This tool returns at most 1 row per message. You would have to write some custom code to do this.

The code in the example below will create a file on the desktop called out.csv which will be a comma separated list of the fields

string _fileName = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop),"out.csv");
StreamWriter _fileWriter;

public override void OnStart() 
{
   _fileWriter = new StreamWriter(_fileName,false);
}

public override void Run() 
{   
  // Get an HL7 Message in parsed format
  HL7Message message = GetParsedMessage();
  
  // skip any test messages
  if(message.MSH[10]=="T")
    return;
  
  PID pid = message.Segments.First<PID>();
  if(pid == null)
    return;
  
  // Get the first PID segment
  foreach(OBR obr in message.Segments.OfType<OBR>())
  {
     _fileWriter.Write(pid[5,1,1]);
     _fileWriter.Write(", ");
     _fileWriter.Write(pid[5,1,2]);
     _fileWriter.Write(", ");
     _fileWriter.Write(obr.Segment.Path.Repeat);
     _fileWriter.Write(", ");
     _fileWriter.Write(obr[2,1,1]);
     _fileWriter.Write(", ");
     _fileWriter.Write(obr[2,1,2]);
     _fileWriter.WriteLine();
  }
  
}

public override void OnFinish() 
{
  if(_fileWriter != null)
  {    
    _fileWriter.Close(); // close the file
    _fileWriter = null;
    // launch notepad to display the file.
    System.Diagnostics.Process.Start("Notepad",_fileName);
  }
}