SQL Server Extend Integer given conditions

czq61nw1 于 12个月前发布在其他

关注(0)|答案(1)|浏览(130)

I would like to extend IDs with a number, e.g. 007. But only in the event that GRP = 3 occurs in an ID . Then only the rows for these IDs should be changed where GRP != 3 and DAT IS NULL .

The data is available in this form
| ID | DAT | GRP |
| ------------ | ------------ | ------------ |
| 1 | 100 | 3 |
| 1 | null | 1 |
| 1 | null | 1 |
| 2 | 200 | 3 |
| 2 | null | 2 |
| 3 | null | 1 |
| 3 | null | 1 |

I expect the following

ID	DAT	GRP
1	100	3
1007	null	1
1007	null	1
2	200	3
2007	null	2
3	null	1
3	null	1

db<>fiddle

My approach which just adds 007. So for ID 1 the new ID is 8 instead of 1007. Further, ID 3 is missing due to the WHERE EXISTS clause.

SELECT *,
  CASE
  WHEN DAT IS NULL AND GRP != 3 THEN ID + 007 ELSE ID END AS ID_NEW
  FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table1 t2
              WHERE t1.ID = t2.ID
              AND GRP = 3
             )

Result of my approach

ID	DAT	GRP	ID_NEW
1	100	3	1
1	null	1	8
1	null	1	8
2	200	3	2
2	null	2	9

Thank you very much for your support

sql-server

来源：https://stackoverflow.com/questions/77618960/extend-integer-given-conditions

1条答案

按热度按时间

vfh0ocws1#

Here is a way to do it using LEFT JOIN :

SELECT CASE WHEN s.id IS NOT NULL THEN CONCAT(t.ID, '007') ELSE t.ID END AS ID, DAT, GRP
FROM table1 t
LEFT JOIN (
  SELECT ID
  FROM table1
  WHERE GRP = 3
) AS s on s.ID = t.ID AND GRP != 3 and DAT IS NULL;

Results :

ID      DAT     GRP
1       100     3
1007    null    1
1007    null    1
2       200     3
2007    null    2
3       null    1
3       null    1

Demo here

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Extend Integer given conditions

1条答案

相关问题

热门标签

最新问答