访问SQl​​服务器中的数据透视表中的哈希表列

I want my data to be display like this

我希望我的数据显示如下

1 | 2 | 3 | 4 ...... P | p | A | P......

1 | 2 | 3 | 4 ...... P | p | A | P ......

actually i want 31 days Attendance in pivoted way. i wrote this query below

实际上我想要31天的出席会议。我在下面写了这个查询

SELECT 
  ROW_NUMBER() OVER(ORDER BY EmpId ASC) AS RowNo,
  CourseID INTO #Mytab
FROM tblEnrolment_Learner

select * 
from 
(
    SELECT  CourseID
  FROM #Mytab ap
) src
pivot
(
  COUNT(CourseID)
  for RowNo in ([1],[2])
) piv ;

DROP table #Mytab

this was a practice query which was failed and i got this error

这是一个失败的练习查询,我收到了这个错误

Invalid column name 'RowNo'.

列名称'RowNo'无效。

Unable to access hashtable column in Pivot. where i am wrong ??

无法访问Pivot中的哈希表列。哪里我错了?

1 个解决方案

#1

You are dropping the column prior to use in pivot. Try it like:

您在使用数据透视表之前删除了列。试试吧:

SELECT 
  ROW_NUMBER() OVER(ORDER BY EmpId ASC) AS RowNo,
  CourseID INTO #Mytab
FROM tblEnrolment_Learner;

SELECT CourseID, [1], [2] FROM #Mytab src
pivot
(
  COUNT(CourseID)
  for RowNo in ([1],[2])
) piv ;

DROP table #Mytab;