从ACCESS TRANSFORM COUNT / PIVOT转换为SQL SERVER 2005 [英] Convert from ACCESS TRANSFORM COUNT/PIVOT to SQL SERVER 2005

问题描述

需要一些帮助从访问 TRANSFORM COUNT / PIVOT 转换为 SQL SERVER ，这里是来自访问的sql：

Need some help converting from access TRANSFORM COUNT/PIVOT to SQL SERVER and here's the sql from access :

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:

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

这是什么发现 3 EPA 1 计数， 4 EPA 2 计数IN 'tmpTbl'表。



感谢您提前！

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

THANK YOU IN ADVANCE!

推荐答案

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 ?

这篇关于从ACCESS TRANSFORM COUNT / PIVOT转换为SQL SERVER 2005的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！