How to write a condition in SQL using the COUNT value

I created some test data like this:

 id_pk | name | id_fk |  name  
-------+------+-------+--------
     1 | foo  |     1 | jingle
     1 | foo  |     1 | jangle
     2 | bar  |     2 | jangle
     3 | quux |       | 

And wrote a query:

select m.id_pk, case when groupcount.name_count > 1 then substring(m.name, 1, 1) + '_' + n.name else m.name end
from test_table1 m
  left join test_table2 n on m.id_pk = n.id_fk
  left join (select name, count(name) as name_count from test_table2 group by name) groupcount on n.name = groupcount.name

That produces this:

 id_pk |   name   
-------+----------
     1 | foo
     1 | f_jangle
     2 | b_jangle
     3 | quux

I think you might have meant m.name rather than n.name in your "else" branch?

I'm guessing (and this is why example output would have been useful) from your "@count should take one column value at a time" that you need to be deriving @count from the count of rows for each name in test_table2.

I believe the output of that query is what you want to go into your temp table: in which case, prefix it with "insert into #temp_cols".

Here's yet another way. If the idea is display the name differently based on whether there are duplicates in TestTable2, then this will work:

DECLARE @Count int
SELECT @Count = COUNT(name) FROM test_table2 GROUP BY name HAVING COUNT(name) > 1

INSERT INTO #temp_cols  
SELECT 
   M.ID_PK,  
   CASE 
      WHEN @Count > 1 THEN SUBSTRING(M.NAME, 1, 1) + '_' + N.NAME
      ELSE N.Name
   END,    
FROM TEST_TABLE1 M WITH (NOLOCK) 
  LEFT JOIN TEST_TABLE2 N  ON M.ID_PK = N.ID_FK 

EDIT: If on the other hand, the intention is to change the display of the name only when there are matching IDs in the TestTable2, this will work and it is very simple:

INSERT INTO #temp_cols  
SELECT 
   M.ID_PK,  
   ISNULL(SUBSTRING(M.NAME, 1, 1) + '_' + N.NAME, M.Name) As Name   
FROM TEST_TABLE1 M WITH (NOLOCK) 
  LEFT JOIN TEST_TABLE2 N  ON M.ID_PK = N.ID_FK

IF EXISTS (select count(name) from test_table2 group by name HAVING count(name) > 1)
BEGIN
  insert into #temp_cols  
  SELECT M.ID_PK,  
  substring(M.NAME,1,1)+'_'+ N.NAME 
  FROM TEST_TABLE1 M WITH (NOLOCK) 
  LEFT JOIN TEST_TABLE2 N  ON M.ID_PK=N.ID_FK  
END

ELSE
BEGIN
  insert into #temp_cols  
  SELECT M.ID_PK,  
  N.NAME 
  FROM TEST_TABLE1 M WITH (NOLOCK) 
  LEFT JOIN TEST_TABLE2 N  ON M.ID_PK=N.ID_FK 
END