I have no idea what "
Create a non aggregate PIVOT
" means...
The best way is to use
PIVOT[
^]
SELECT KeyValue, IDP, ID1, IDS, ID2
FROM
(
SELECT KeyValue, FieldName, FieldValue
FROM KFF
) src
PIVOT(MAX(FieldValue) FOR FieldName IN(IDP, ID1, IDS, ID2)) pvt
SQL Server 2019 | db<>fiddle[
^]
If you don't want to use PIVOT, you need to use multiple joins!
SELECT DISTINCT k.KeyValue, t1.IDP, t2.ID1, t3.IDS, t4.ID2
FROM KFF k INNER JOIN
(
SELECT KeyValue, FieldValue IDP
FROM KFF
WHERE FieldName = 'IDP'
) t1 ON k.KeyValue = t1.KeyValue
INNER JOIN
(
SELECT KeyValue, FieldValue ID1
FROM KFF
WHERE FieldName = 'ID1'
) t2 ON k.KeyValue = t2.KeyValue
INNER JOIN
(
SELECT KeyValue, FieldValue IDS
FROM KFF
WHERE FieldName = 'IDS'
) t3 ON k.KeyValue = t3.KeyValue
INNER JOIN
(
SELECT KeyValue, FieldValue ID2
FROM KFF
WHERE FieldName = 'ID2'
) t4 ON k.KeyValue = t4.KeyValue
SQL Server 2019 | db<>fiddle[
^]