Convert the Pivot table in MS Access to Pivot table in SQL Server

Question

I need to convert some SQL statement from MS Access to SQL Server using a pivot table. I have a view [UETTDRSB39A Criteria] and a table [RPLEvidence options].

I have never tried using pivot tables in SQL Server.

The MS Access code is:

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

The desired (Access) output is:

The Incorrect SQL Server Query is:

use LinesmanRPL
SELECT * from
(
Select distinct [RPL Link] as expr1, [RPL Link], [RPLEvidenceOptionsDetail], [Prerequisite] 
from [UETTDRSB39A Criteria], [2_Candidate Evidence records] 
where [UETTDRSB39A Criteria].Pathway = 'Common' or [UETTDRSB39A Criteria].Pathway = 'Electrical'
)temp
PIVOT  
(  
min(temp.expr1)  
FOR temp.[Prerequisite]
IN (Prereq1, Prereq2, Prereq3)  
) AS p
order by [RPL Link]

The actual (SQL) output is:

The sample data for UETTDRSB39A Criteria

The sample data for 2_Candidate Evidence records

I also tried another SQL code:

use LinesmanRPL
DECLARE @PivotColumnHeaders VARCHAR(max);
SELECT distinct @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',['+ CAST(Prerequisite as varchar) + ']','['+ CAST(Prerequisite as varchar) + ']','['+ CAST(Prerequisite as varchar) + ']')
FROM [UETTDRSB39A Criteria]
where [UETTDRSB39A Criteria].Pathway='Common' or [UETTDRSB39A Criteria].Pathway='Electrical';
DECLARE @PivotTableSQL NVARCHAR(max);
SET @PivotTableSQL = N'
SELECT distinct *
FROM (
SELECT DISTINCT
    U.[RPL Link],
    U.Prerequisite,
    U.[RPLEvidenceOptionsDetail]
FROM [UETTDRSB39A Criteria] as U, [2_Candidate Evidence records] as C
    ) as pivotData
    pivot(
        min([RPL Link])
        for Prerequisite in (' + @PivotColumnHeaders +')
    ) as pivotTable    
'
;
execute(@PivotTableSQL);

The output of this code is:

You're pretty close, your first example fails because the pivot column names don't exist in the query. Your second example doesn't work because it only picks the first dynamic column. Here's an example of how to build up the column list dynamically: https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Richard Hansell, Apr 04 '19 at 08:40

score 1 · Answer 1 · answered Apr 04 '19 at 12:12

I think you have made it unnecessarily complex. I am pretty sure that all you need is the following:

SELECT * from
(
Select distinct RPL_Link as expr1, RPL_Link, RPLEvidence_Options_Detail, Prerequisite 
from UETTDRSB39A_Criteria
where UETTDRSB39A_Criteria.Pathway = 'Common' or UETTDRSB39A_Criteria.Pathway = 'Electrical'
)temp
PIVOT  
(  
min(temp.expr1)  
FOR temp.[Prerequisite]
IN ([UETTDREL16A], [UETTDRIS67A], [UETTDRSB39A])  
) AS p
ORDER BY RPL_LINK

Please note that in importing your test data, I used underscores instead of blank spaces, but I think it should be clear what you need to do.

HTH

thank you for your solution. However, another solution was more flexible as it creates dynamic tables. — Twini, Apr 21 '19 at 03:54

score 1 · Accepted Answer · answered Apr 18 '19 at 15:53

After looking at the solution provided by @Jonathan Willcock, I was able to make up a solution for your problem. Have a look at this and perhaps you might need to refine it.

USE LinesmanRPL
DECLARE @PrerequisiteColumn AS NVARCHAR(MAX),
        @SQLforPivotTable  AS NVARCHAR(MAX);
SET @PrerequisiteColumn = STUFF((SELECT distinct ',' + QUOTENAME(Prerequisite) 
        FROM [UETTDRSB39A Criteria]
        WHERE [UETTDRSB39A Criteria].Pathway = 'Common' OR [UETTDRSB39A Criteria].Pathway = 'Electrical'
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @SQLforPivotTable = 'SELECT RPLEvidenceOptionsDetail, ' + @PrerequisiteColumn + ' FROM 
        (
          SELECT [RPL Link], RPLEvidenceOptionsDetail, Prerequisite 
          FROM [UETTDRSB39A Criteria]
        ) Temp
        PIVOT 
        (
          MIN([RPL Link]) FOR Prerequisite IN (' + @PrerequisiteColumn + ')
        ) PivotTable '
EXECUTE(@SQLforPivotTable)

Thank you @Tejas, it worked! Do you think if I can hide the rows that have null in all the three columns? — Twini, Apr 21 '19 at 03:53

Convert the Pivot table in MS Access to Pivot table in SQL Server

2 Answers2