title: Transact SQL: String concatenation within ordered rows with certain logic
body: <p>Given the table</p>
  
  <pre><code>CREATE TABLE dbo.T
  (
      CN char(2) NOT NULL,
      GN int NOT NULL,
      SG int NOT NULL,
      SN int NOT NULL,
      C varchar(3),
      CB varchar(max)
  );
  INSERT INTO dbo.T(CN,GN,SG,SN,C,CB)
  VALUES
  ('P1',9,6,0,'020','AAA'),
  ('P1',9,6,1,NULL,'BBB'),
  ('P1',9,6,2,'020','CCC'),
  ('P1',9,6,3,NULL,'DDD'),
  ('P1',9,6,4,'020','EEE'),
  ('P1',9,6,5,NULL,'FFF'),
  ('P1',9,6,6,'020','GGG'),
  ('P1',9,6,7,NULL,'HHH'),
  ('P1',9,6,8,'020','JJJ'),
  ('P1',9,6,9,NULL,'LLL'),
  ('P1',9,6,10,NULL,'MMM'),
  ('P1',9,6,11,NULL,'NNN')
  </code></pre>
  
  <p>the desired output is this:</p>
  
  <pre><code>CN  GN  SG  SN  C    CB
  P1  9   6   0   020  AAABBB
  P1  9   6   2   020  CCCDDD
  P1  9   6   4   020  EEEFFF
  P1  9   6   6   020  GGGHHH
  P1  9   6   8   020  JJJLLLMMMNNN
  </code></pre>
  
  <p>So you go through all the rows ordered by <code>CN</code>,<code>GN</code>,<code>SG</code>,<code>SN</code>. Start with the first occurence of a no NULL value in <code>C</code>. When the following row got a NULL value in column <code>C</code> then concatenate the <code>CB</code> values as long as the following row is not NULL in column <code>C</code>.</p>