开发者

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)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜