Translate MS Access TRANSFORM statement to T-SQL

Question

I am trying to translate the following Query from MS Access to T-SQL:

TRANSFORM COUNT(TableA.Primary) AS CountOfPrimary
SELECT 
TableA.Id1
,TableA.Id2
FROM TableA
GROUP BY TableA.Id1, TableA.Id2
ORDER BY TableA.Id1
PIVOT TableA.Primary;

Using this thread write a TRANSFORM statement in Sql Server and this other resource https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/ I've gotten to these 2 separate points, I think the first with the case statements is working accurately, but how do I create the pivot version in T-SQL?

SELECT DISTINCT 
    Id1
    ,Id2
    ,'1' = MAX(CASE WHEN Primary = '1' THEN 1 ELSE NULL END)
    ,'0' = MAX(CASE WHEN Primary = '0' THEN 1 ELSE NULL END)
FROM TableA
GROUP BY Id1, Id2
ORDER BY Id1

SELECT *
FROM (
    SELECT 
        Id1
        ,Id2
        ,CountOfPrimary = COUNT(Primary)
    FROM TableA
) ts
PIVOT (
    COUNT(Primary)
    FOR CountOfPrimary IN (0,1)
) pt

This my initial data table:

These are the results I expect to get:

Thank you in advance for any help/advice!

BJones · Accepted Answer

You were close and on the right track. Also, be careful using reserved keywords such as 'Primary' for column names. See if the following works.

DECLARE @tab TABLE (Id1 VARCHAR(10), Id2 VARCHAR(10), Pri INT)
INSERT @tab VALUES
('8574','9401',1),
('2154','5604',1),
('3129','6255',1),
('2714','7750',1),
('3902','9585',1),
('0535','6880',1),
('0765','9401',1),
('5946','9401',1),
('7074','9585',1),
('7181','9585',0),
('7181','9585',1),
('8635','9401',1),
('0334','4433',0),
('0334','4433',1),
('0623','5604',1)


SELECT Id1, Id2, [1], CASE WHEN [0] = 0 THEN NULL ELSE [0] END AS [0]
FROM (
    SELECT 
        Id1
        ,Id2
        ,Pri
    FROM @tab
) ts
PIVOT (
    COUNT(Pri)
    FOR Pri IN ([1],[0])
) pt

Translate MS Access TRANSFORM statement to T-SQL

Answers (1)

Related Questions