question

TP2020 avatar image
TP2020 asked

i need to convert a pivot query from access to sql but can't get the syntax right please help?

TRANSFORM First(Val([PartData])) AS Expr1 
SELECT 
 a_tblParts.CarID
FROM 
 a_tblParts 
  INNER JOIN a_tblPartHead 
   ON a_tblParts.PartID = a_tblPartHead.PartID
WHERE 
 (((a_tblPartHead.Part) In 
  ("GA ","Legal","GV","LRating","Rear","Legal A","GC","Legal D")))
GROUP BY 
 a_tblParts.CarID
PIVOT 
 a_tblPartHead.Part;
pivotsql querysyntaxconvert
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

Yeah,

PIVOT in Transact-SQL is a completely different beast than PIVOT in Access.

I'm not sure what kind of data you have in your table, but it will look a little something like this (with possible needs for some tweaking depending on data in your table).

If I remember correctly how the PIVOT function works in Access, I think this will get you where you want.

Note that I assume PartData contains a string value storing integer values. That might not be the case, you use Val in your Access query, but Val, if my memory is not cheating me, will return a numeric value and if that numeric is a decimal value, a decimal value will be returned. So it might be you want to cast to numeric (5,2) or something like that instead.

Note also that Val in Access is very forgiving while CAST is not. So if you have something like '5 mm' stored in PartData, your query won't work. The string value have to be a proper numeric value. The PARSE function in T-SQL is a bit more forgiving than CAST but not as forgiving as Val in Access.

SELECT
  a_tblParts.CarID,
  [GA],
  [Legal],
  [GV],
  [LRating],
  [Rear],
  [Legal A],
  [GC],
  [Legal D]
FROM (
  SELECT 
   a_TblParts.CarID,
   a_TblPartHead.Part,
   a_TblPartHead.PartData
  FROM
   a_TblParts
    INNER JOIN a_TblPartHead
     ON a_TblParts.PartID = a_TblPartHead.PartID
  WHERE
   a_TblPartHead.Part IN ('GA','Legal','GV','LRating','Rear','Legal A','GC','Legal D')
) as t
PIVOT(MIN(CAST(PartData as int))
FOR Part IN ([GA],[Legal],[GV],[LRating],[Rear],[Legal A],[GC],[Legal D])) as p
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.