英文:
Cross apply openjson / pivot - multiple entries
问题
以下是您要翻译的代码部分：
WITH request
as
(
SELECT requestId,
property1191,
'['+replace(replace(property1191, '[', ''), ']', '')+']' as json
from capex_management_requests
)
SELECT *
FROM
(
SELECT
P.requestId,
AttsData.[Id],
AttsData.[data],
ROW_NUMBER() OVER (PARTITION BY P.requestId, AttsData.[Id]
ORDER BY CAST(arr.[key] AS int)) AS row_id
FROM request P
CROSS APPLY OPENJSON (P.json) AS arr
CROSS APPLY OPENJSON (arr.value)
WITH
(
Id VARCHAR(200) N'$.metaId',
data VARCHAR(200)
) AS AttsData
) DS
PIVOT
(
MAX(data) FOR Id IN ([690], [1192])
) piv;
请注意，这是您提供的代码的翻译版本。
英文:
The following code, written by Charlieface in this answer, converts and opens a json content and pivots it in the end. It could be the case that multpiple entries can occur. How does the code need to be adapted to cover that as well?
>
> WITH request
> as
> (
> SELECT requestId,
> property1191,
> '['+replace(replace(property1191, '[', ''), ']', '')+']' as json
> from capex_management_requests
> )
> SELECT *
> FROM
> (
> SELECT
> P.requestId,
> AttsData.[Id],
> AttsData.[data],
> ROW_NUMBER() OVER (PARTITION BY P.requestId, AttsData.[Id]
> ORDER BY CAST(arr.[key] AS int)) AS row_id
> FROM request P
> CROSS APPLY OPENJSON (P.json) AS arr
> CROSS APPLY OPENJSON (arr.value)
> WITH
> (
> Id VARCHAR(200) N'$.metaId',
> data VARCHAR(200)
> ) AS AttsData
> ) DS
> PIVOT
> (
> MAX(data) FOR Id IN ([690], [1192])
> ) piv;
>
Current outcome - only "MAX" value for each ID [690] value
| requestId | row_id | 690 | 1192 |
|---|---|---|---|
| 1 | x | 1 | 4352 |
| 1 | x | 2 | 3887 |
| 1 | x | 3 | 4372 |
| 1 | x | 4 | 3749 |
| 1 | x | 51 | 3693 |
| 1 | x | 89 | 4228 |
Target - multiple entries instead of "MAX" for each ID [690] value
| requestId | row_id | 690 | 1192 |
|---|---|---|---|
| 1 | x | 1 | 4100 |
| 1 | x | 1 | 4352 |
| 1 | x | 2 | 3887 |
| 1 | x | 3 | 4200 |
| 1 | x | 3 | 4300 |
| 1 | x | 3 | 4372 |
| 1 | x | 4 | 3749 |
| 1 | x | 51 | 3693 |
| 1 | x | 51 | 3712 |
| 1 | x | 89 | 4228 |
I want to be able to also cover multiple entries correctly!
答案1
得分: 1
以下是代码的部分翻译：
WITH request
as
(
SELECT requestId,
property1191,
'['+replace(replace(property1191, '[', ''), ']', '')+']' as json
from capex_management_requests
),
ds AS (
SELECT
P.requestId,
AttsData.[Id],
AttsData.[data],
ROW_NUMBER() OVER (PARTITION BY P.requestId, AttsData.[Id]
ORDER BY CAST(arr.[key] AS int)) AS row_id
FROM request P
CROSS APPLY OPENJSON (P.json) AS arr
CROSS APPLY OPENJSON (arr.value)
WITH
(
Id VARCHAR(200) N'$.metaId',
data VARCHAR(200)
) AS AttsData
)
SELECT
requestId,
data,
row_id,
CASE WHEN Id = '690' THEN data ELSE NULL END AS [690],
CASE WHEN Id = '1192' THEN data ELSE NULL END AS [1192]
FROM ds
希望这对你有所帮助。
英文:
If I understand the question, I believe you would avoid the PIVOT to avoid the MAX aggregation. You can use CASE statements to pick out the values that the pivot would have. I put the subquery as another CTE to perhaps make it clearer what's going on.
WITH request
as
(
SELECT requestId,
property1191,
'['+replace(replace(property1191, '[', ''), ']', '')+']' as json
from capex_management_requests
),
ds AS (
SELECT
P.requestId,
AttsData.[Id],
AttsData.[data],
ROW_NUMBER() OVER (PARTITION BY P.requestId, AttsData.[Id]
ORDER BY CAST(arr.[key] AS int)) AS row_id
FROM request P
CROSS APPLY OPENJSON (P.json) AS arr
CROSS APPLY OPENJSON (arr.value)
WITH
(
Id VARCHAR(200) N'$.metaId',
data VARCHAR(200)
) AS AttsData
)
SELECT
requestId,
data,
row_id,
CASE WHEN Id = '690' THEN data ELSE NULL END AS [690],
CASE WHEN Id = '1192' THEN data ELSE NULL END AS [1192]
FROM ds
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论