Convert the AccessDB query into T-SQL query

I have a question about Transform and Pivot. I need to convert the following Access DB query to T-SQL:

TRANSFORM First([UETTDRSB39A Criteria].[RPL Link]) AS [FirstOfRPL Link]
SELECT [UETTDRSB39A Criteria].[RPL Link], [UETTDRSB39A Criteria].Detail1
FROM [UETTDRSB39A Criteria], [Candidate Evidence records]
WHERE ((([UETTDRSB39A Criteria].Pathway) In ('Common','Electrical')))
GROUP BY [UETTDRSB39A Criteria].[RPL Link], [UETTDRSB39A Criteria].Detail1
PIVOT [UETTDRSB39A Criteria].Prerequisite;

I tried converting the query, however, I get some errors.

select pt.* 
from (

SELECT [UETTDRSB39A Criteria].[RPLLink], [UETTDRSB39A Criteria].Detail1, Prerequisite

FROM [UETTDRSB39A Criteria], [CandidateEvidenceRecords]

WHERE ((([UETTDRSB39A Criteria].Pathway) In ('Common','Electrical')))

GROUP BY [UETTDRSB39A Criteria].[RPLLink], [UETTDRSB39A Criteria].Detail1
) src

pivot (FIRST(RPLLink) for src.Prerequisite in ([UEENEEE101],[UEENEEE102],[UEENEEE104],[UEENEEE105],[UEENEEE107],[UEENEEE137],
[UEENEEG006],[UEENEEG033],[UEENEEG063],[UEENEEG101],[UEENEEG102],[UEENEEG106],[UEENEEG108], [UEENEEG109],
[UEENEEK142],[UETTDREL16A],[UETTDRIS67A],[UETTDRSB39A])) pt;

The error reads 'first is not an aggregate function'. However, when I enter:

select pt.* 
from (
SELECT [UETTDRSB39A Criteria].[RPLLink], [UETTDRSB39A Criteria].Detail1, Prerequisite

FROM [UETTDRSB39A Criteria], [CandidateEvidenceRecords]

WHERE ((([UETTDRSB39A Criteria].Pathway) In ('Common','Electrical')))

GROUP BY [UETTDRSB39A Criteria].[RPLLink], [UETTDRSB39A Criteria].Detail1
) src

pivot (top 1 (RPLLink) for src.Prerequisite in ([UEENEEE101],[UEENEEE102],[UEENEEE104],[UEENEEE105],[UEENEEE107],[UEENEEE137],
[UEENEEG006],[UEENEEG033],[UEENEEG063],[UEENEEG101],[UEENEEG102],[UEENEEG106],[UEENEEG108], [UEENEEG109], [UEENEEK142],[UETTDREL16A],[UETTDRIS67A],[UETTDRSB39A])) pt;

The table schema:

CREATE TABLE [dbo].[UETTDRSB39A Criteria](
[UoC] [nvarchar](50) NULL,
[AncestorLevel] [int] NULL,
[Pathway] [nvarchar](100) NULL,
[Prerequisite] [nvarchar](50) NULL,
[Title] [nvarchar](500) NULL,
[Descriptor] [nvarchar](4000) NULL,
[UoCType] [nvarchar](50) NULL,
[Summary] [nvarchar](4000) NULL,
[Ref] [nvarchar](50) NULL,
[Detail] [nvarchar](4000) NULL,
[Type1] [nvarchar](800) NULL,
[RPLType] [nvarchar](500) NULL,
[RPLPreferred] [nvarchar](2000) NULL,
[RPLLink] [nvarchar](50) NULL,
[Detail1] [nvarchar](4000) NULL
)


CREATE TABLE [dbo].[CandidateEvidenceRecords](
[ID] [int] NULL,
[EvidenceID] [nvarchar](50) NULL,
[Candidate] [nvarchar](150) NULL,
[EvidenceType] [nvarchar](1000) NULL,
[UoC] [nvarchar](50) NULL,
[Evidence] [nvarchar](2000) NULL,
[DateOfIssue] [date] NULL,
[DateOfExpiry] [date] NULL,
[Issuer] [nvarchar](1500) NULL,
[CertificateNumber] [nvarchar](500) NULL,
[Confirmed] [bit] NULL,
[ConfirmedBy] [nvarchar](100) NULL,
[ConfirmedDate] [date] NULL
)

It still does not work.

Looking forward to your response.

Thank you.

1 answer

  • answered 2018-02-13 06:39 Erik von Asmuth

    You can use Max() instead of First(), if there's only one value anyway.

    If there are multiple values, and you want the first value ordered by a certain key, use a subquery to filter out the other values and then use Max()