Create a non aggregate PIVOT MS SQL query

Question

0.00/5 (No votes)

See more:

I have this data or records as below

KeyValue                 FieldName                FieldValue
001068                   ID1                      16086
001068                   ID2                      1142201
001068                   IDP                      Senior Citizen Card
001068                   IDS                      AFP
002224                   IDS                      VIN
002224                   IDP                      Social Security Services
002224                   ID2                      804-0073G0665AFI1000
002224                   ID1                      021-1694-7770-6

And I want to have an output like this

KeyValue   IDP                       ID1              IDS      ID2
001068     Senior Citizen Card       16086            AFP      1142201
002224     Social Security Services  021-1694-7770-6  VIN      804-0073G0665AFI1000

What I have tried:

I hope to receive a reply.

Thank you

Posted 14-Mar-21 3:15am

tadyaw

Updated 14-Mar-21 23:09pm

OriginalGriff

v2

Add a Solution

Comments

OriginalGriff 14-Mar-21 9:26am

What have you tried?
Where are you stuck?
What help do you need?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2021-03-14T23:09:00

I have no idea what "Create a non aggregate PIVOT" means...

The best way is to use PIVOT[^]

SQL

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!

SQL

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[^]