SQL PIVOT not as suspected-CodePudding

I'm working on a data extraction of multiple tables for so far i have this

Table A

ID	value
1	123

Table B

ID	FileID	FieldID	A_ID	Data
1	1	1	1	abc
2	1	2	1	123
3	1	3	1	TRUE
4	1	1	2	def
5	1	2	2	456
6	1	3	2	FALSE

so far I have

SELECT Id, Plate, [1] as field1, [2] as field2, [3] as field3 FROM 
   (SELECT A.Id, A.value , B.Data as datavalue, B.Id as dataid
    FROM TableA A, TableB B 
    WHERE A.Id = B.A_ID)as P


PIVOT (MAX(dataid) FOR datavalue in ([1],[2],[3])) as  PVT

the answer I expect

Id	Value	Field1	Field2	Field3
1	123	abc	123	TRUE

the answer I get

Id	Value	Field1	Field2	Field3
1	123	NULL	NULL	NULL

what am i missing

can't get it figured out

CodePudding user response：

You need to LEFT JOIN from: Table B to Table A instead of Table A to Table B

Switch the two tables around within the nested select.

CodePudding user response：

Use Group by and Max function and change pivot code to :

PIVOT (MAX(datavalue) FOR datavalue in ([abc],[123],[TRUE])) AS  PVT

SELECT Id, 
       value, 
       max([abc]) as field1,
       max([123]) as field2,
       max([TRUE]) as field3 
FROM 
   (SELECT A.Id, A.value , B.Data AS datavalue, B.Id AS dataid
    FROM TableA A, TableB B 
    WHERE A.Id = B.A_ID) AS P


PIVOT (MAX(datavalue) FOR datavalue in ([abc],[123],[TRUE])) AS  PVT
GROUP BY id,value

Demo in db<>fiddle

CodePudding user response：

I believe you meant B.FieldId instead of B.Id. Other than that you need slight modification (you got the dataId and dataValue wrong, it would be

... Max(datavalue) for dataId in ...

instead of

... Max(dataId) for dataValue in ...

Here is revised version:

SELECT
  ID, PVT.value, [1] AS field1, [2] AS field2, [3] AS field3
FROM
  (
    SELECT
         A.ID, A.value, B.Data AS datavalue, B.FieldId AS dataid
    FROM TableA            A
         INNER JOIN TableB B ON A.ID=B.A_ID
  ) AS P
PIVOT(MAX(datavalue)
FOR dataid IN([1], [2], [3])
     ) AS PVT;

Here is DBFiddle demo.