更新时间:2023-02-07 13:32:21
您可以在属性名称中添加一个行号,该行号将允许您执行所需的操作:
You can add a row number to the propertyname that will allow you to do what you want:
SELECT * FROM
(
SELECT ENTITYID
, PROPERTYNAME = PROPERTYNAME + CAST(ROW_NUMBER() OVER(PARTITION BY ENTITYID, PROPERTYNAME ORDER BY PROPERTYVALUE) AS VARCHAR(5))
,PROPERTYVALUE
FROM #STAGING
) AS T
PIVOT (MAX(PROPERTYVALUE) FOR PROPERTYNAME IN (NAME1, SPOUSE1, CHILD1, CHILD2, CHILD3, CHILD4, CHILD5)) AS T2
我在这里假设ENTITYID将孩子与父母联系起来,即同一个人的所有孩子的ENTITYID为1,但是您的示例为Kayala展示了2.
I'm assuming here that the ENTITYID ties the children to the parent, ie all children for the same person have ENTITYID of 1, but your example shows a 2 for Kayala.
这里是一个演示: SQL Fiddle
如果您只想要CHILD字段的数字,则可以输入以下内容:
If you only want the numbers for the CHILD fields you could put this:
PROPERTYNAME = CASE WHEN PROPERTYNAME LIKE '%CHILD%' THEN PROPERTYNAME + CAST(ROW_NUMBER() OVER(PARTITION BY ENTITYID, PROPERTYNAME ORDER BY PROPERTYVALUE) AS VARCHAR(5)) ELSE PROPERTYNAME END
然后从IN()语句中的其他字段中删除该数字.
Then remove the number from the other fields in your IN() statement.
奖金问题-动态执行上述操作: 我们不想假设人们只有一个配偶或2.3个孩子,因此我们会动态地进行全部操作:
Bonus Question- Do the above dynamically: We don't want to assume that people only have one spouse or 2.3 children, so we do the whole bit dynamically:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT ',' + PROPERTYNAME
FROM (SELECT DISTINCT PROPERTYNAME = PROPERTYNAME + CAST(ROW_NUMBER() OVER(PARTITION BY ENTITYID, PROPERTYNAME ORDER BY PROPERTYVALUE) AS VARCHAR(5))
FROM STAGING )sub
ORDER BY CASE WHEN PROPERTYNAME LIKE '%NAME%' THEN 1
WHEN PROPERTYNAME LIKE '%SPOUSE%' THEN 2
WHEN PROPERTYNAME LIKE '%CHILD%' THEN 3
ELSE 4
END
,RIGHT(PROPERTYNAME,1)
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query = 'SELECT * FROM
(
SELECT ENTITYID, PROPERTYNAME = PROPERTYNAME + CAST(ROW_NUMBER() OVER(PARTITION BY ENTITYID, PROPERTYNAME ORDER BY PROPERTYVALUE) AS VARCHAR(5)),PROPERTYVALUE
FROM STAGING
) AS T
PIVOT (MAX(PROPERTYVALUE) FOR PROPERTYNAME IN ('+@cols+')) AS T2
'
EXEC(@query)
注意:此顺序仅适用于1-9岁的配偶和1-9岁的孩子,您可以对其进行调整以适合自己,但这仍然是任意的.
Note: The ordering will only work for spouses 1-9 and children 1-9, you can adjust that to suit, but it's arbitrary anyway.