15,797,923 members
See more:
Need some help converting from access TRANSFORM COUNT/PIVOT to SQL SERVER and here's the sql from access :

SQL
```TRANSFORM Count(tmpTbl.TC) AS CountOfTC
SELECT tmpTbl.SID, tmpTbl.CSID, tmpTbl.M,WoOr.QCL
FROM tmpTbl INNER JOIN WoOr ON tmpTbl.WO = WoOr.WO
WHERE (((tmpTbl.IsSelected)=True))
GROUP BY tmpTbl.SID, tmpTbl.CSID, tmpTbl.M,WoOr.QCL
PIVOT tmpTbl.TN;```

OUTPUT:

SQL
```SID |   CSID | M |   QCL | EPA 1 | EPA 2 |
-----------------------------------------|
1   |   A    |GW |  IV   |  3    |       |
2   |   B    |GW |  IV   |       |  4    |
------------------------------------------```

WHere there are 3 EPA 1 count found and 4 EPA 2 count found IN 'tmpTbl' Table.

Posted

## Solution 1

SQL
```SELECT tmpTbl.SID, tmpTbl.CSID, tmpTbl.M,WoOr.QCL, COUNT(epa1), COUNT(epa2)
FROM tmpTbl INNER JOIN WoOr ON tmpTbl.WO = WoOr.WO
WHERE (((tmpTbl.IsSelected)=True))
GROUP BY tmpTbl.SID, tmpTbl.CSID, tmpTbl.M,WoOr.QCL```

is what you want to do. It's not a pivot at all, you're already selecting your columns and doing a group by, so you just want the count within the groups, right ?