How do I do `group by` partial match
I have a table in SQL-server with projectcodes and sub-project-codes in the same fields.
The stucture is something like this
+----+------+-------------+-------+--------+--------+
| i开发者_运维百科d | date | projectcode | debit | credit | budget |
+----+------+-------------+-------+--------+--------+
| 1 | bla | A100 | bla
| 2 | bla | A100.01 |
| 3 | bla | A112 |
| 4 | bla | A112.02
How do I do a select like this
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 group by
A100 and A100.01 and A100.x together as well as A112 + A112.x
How do I do this?
I have no control over the structure of the table.
GROUP BY LEFT(projectcode ,CHARINDEX('.',projectcode + '.')-1)
Maybe this would 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)
If the project code always follows the same pattern (cnnn / cnnn.nn) you can just get the first four characters:
group by substring(projectcode, 1, 4)
精彩评论