How to make a partial match "group by"

Question

I have a table on an SQL server with project codes and subprojects in the same fields.

This thing is like this

+----+------+-------------+-------+--------+--------+ | id | date | projectcode | debit | credit | budget | +----+------+-------------+-------+--------+--------+ | 1 | bla | A100 | bla | 2 | bla | A100.01 | | 3 | bla | A112 | | 4 | bla | A112.02 

How to make such a choice

 SELECT projectcode , sum(debit) as debit , sum(credit) as credit , sum(budget) as budget FROM table1 GROUP BY -insert-answer-here- 

I want the output to be group by A100 and A100.01 and A100.x together, as well as A112 + A112.x

How to do it?

I can not control the structure of the table.

Answer 1

 GROUP BY LEFT(projectcode ,CHARINDEX('.',projectcode + '.')-1) 

Answer 2

If the project code always follows the same pattern (cnnn / cnnn.nn), you can simply get the first four characters:

 group by substring(projectcode, 1, 4) 

Answer 3

Maybe this will work:

 SELECT Substring(projectcode, 1, 4) as Project , sum(debit) as debit , sum(credit) as credit , sum(budget) as budget FROM table1 GROUP BY Substring(projectcode, 1, 4)