title: Combine rows if value is blank
body: <p>I'm using <em>SQL-Server 2008</em>. I need to combine rows with the same <code>Name</code> and increase counter when:</p>
  
  <ol>
  <li>1 or more <code>Id's</code> for the same <code>Name</code> is <code>blank</code></li>
  <li>NOT merge rows if <code>Id</code> is <code>NULL</code>!</li>
  <li>NOT merge rows if have the same <code>Name</code>, but different <code>Ids</code></li>
  </ol>
  
  <p><strong>Output for now:</strong></p>
  
  <pre><code>Name    Id   Cnt
  John    1     1
  Peter   2     2 -- This Peter with the same Id have 2 entries so Cnt = 2
  Peter   3     1 -- This is other Peter with 1 entry so Cnt = 1
  Lisa    4     1
  Lisa   NULL   1
  David   5     1
  David         1 -- here Id is blank ''
  Ralph         2 -- Ralph have both rows with blank Id so Cnt = 2
  </code></pre>
  
  <p><strong>Desired output:</strong></p>
  
  <pre><code>Name    Id   Cnt
  John    1     1
  Peter   2     2
  Peter   3     1
  Lisa    4     1
  Lisa   NULL   1 -- null still here
  David   5     2 -- merged with blank '' so Cnt = 2
  Ralph         2 -- merged both blanks '' so Cnt = 2
  </code></pre>
  
  <p><strong>SQL-Query:</strong>                    </p>
  
  <p>This is sample query what I'm using for now:</p>
  
  <pre><code>SELECT Name, 
         Id, 
         COUNT(Id) AS Cnt
  FROM Employees                       
  WHERE Condition = 1                 
  GROUP BY Name, Id
  </code></pre>
  
  <p><strong>What I have tried:</strong></p>
  
  <p>Added aggregate <code>MAX</code> to <code>Id</code> in <code>SELECT</code> clause and grouped by <code>Name</code> only, but in this case merged rows with <code>NULL</code> values and with the same names with different <code>Id's</code> what is wrong for me. </p>
  
  <pre><code>SELECT Name, 
         MAX(Id), -- added aggregate
         COUNT(Id) AS Cnt
  FROM Employees                       
  WHERE Condition = 1                 
  GROUP BY Name -- grouped by Name only
  </code></pre>
  
  <p>Have you any ideas? If anything is not clear about problem - ask me, I will provide more details.</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p><strong>DDL</strong></p>
  
  <pre><code>CREATE TABLE Employees
  (
    Name NVARCHAR(40),
    Id NVARCHAR(40)
  );
  </code></pre>
  
  <p><strong>DML</strong> </p>
  
  <pre><code>INSERT INTO Employees VALUES
   ('John' ,   '1')
  ,('Peter',   '2')
  ,('Peter',   '2')
  ,('Peter',   '3')
  ,('Lisa' ,   '4')
  ,('Lisa' ,  NULL)
  ,('David',   '5')
  ,('David',   '')
  ,('Ralph',   '')
  ,('Ralph',   '')
  </code></pre>
  
  <p><strong>DEMO:</strong>  <kbd><a href="http://sqlfiddle.com/#!3/33587/1" rel="nofollow">SQL FIDDLE</a></kbd></p>