SQL Server how to group by ID and if Type is wo then get the value from case# and copy that value to wo Case# column for all the same Id in SSME

zwghvu4y 于 2023-10-15 发布在其他

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

I am trying to write the SQL query to get the result.

Table A looks like below.
| ID | Type | Case# |
| ------------ | ------------ | ------------ |
| 101 | wo | 23 |
| 101 | tsk | 12 |
| 101 | tsk | 13 |
| 101 | tsk | 14 |
| 101 | tsk | 15 |
| 642 | wo | 500 |
| 642 | tsk | 34 |
| 642 | tsk | 35 |
| 642 | tsk | 36 |
| 642 | tsk | 37 |

Expecting the result

ID	Type	Case#	wo Case#
101	wo	23	23
101	tsk	12	23
101	tsk	13	23
101	tsk	14	23
101	tsk	15	23
642	wo	500	500
642	tsk	34	500
642	tsk	35	500
642	tsk	36	500
642	tsk	37	500

I am not sure how to do this

Select * from TableA

sql-server

来源：https://stackoverflow.com/questions/77290423/how-to-group-by-id-and-if-type-is-wo-then-get-the-value-from-case-and-copy-that

1条答案

按热度按时间

a8jjtwal1#

You can use a window function and conditional aggregation:

SELECT *,
    MAX(CASE WHEN Type = 'wo' THEN [Case#] END) OVER(PARTITION BY ID) AS [wo Case#]
FROM TableA

Results:

ID	Type	Case#	wo Case#
101	wo	23	23
101	tsk	12	23
101	tsk	13	23
101	tsk	14	23
101	tsk	15	23
642	wo	500	500
642	tsk	34	500
642	tsk	35	500
642	tsk	36	500
642	tsk	37	500
777	wo	701	702
777	wo	702	702
777	tsk	703	702
777	tsk	704	702
999	tsk	91	null
999	tsk	92	null

The OVER(PARTITION BY ID) part allows the MAX() to extend its scope to all rows having the same ID value. The CASE...END exclude all values except the one we are interested in.

If there is a chance you might have more than one Type = 'wo' row for a given ID, you will need to consider which value should be used and perhaps modify the window function with an ORDER BY within the OVER() clause.

See this db<>fiddle for a working example with a few extra test data rows.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server how to group by ID and if Type is wo then get the value from case# and copy that value to wo Case# column for all the same Id in SSME

1条答案

相关问题

热门标签

最新问答

ID	Type	Case#	wo Case#
101	wo	23	23
101	tsk	12	23
101	tsk	13	23
101	tsk	14	23
101	tsk	15	23
642	wo	500	500
642	tsk	34	500
642	tsk	35	500
642	tsk	36	500
642	tsk	37	500

ID	Type	Case#	wo Case#
101	wo	23	23
101	tsk	12	23
101	tsk	13	23
101	tsk	14	23
101	tsk	15	23
642	wo	500	500
642	tsk	34	500
642	tsk	35	500
642	tsk	36	500
642	tsk	37	500

ID	Type	Case#	wo Case#
101	wo	23	23
101	tsk	12	23
101	tsk	13	23
101	tsk	14	23
101	tsk	15	23
642	wo	500	500
642	tsk	34	500
642	tsk	35	500
642	tsk	36	500
642	tsk	37	500