StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PONot able to display correct data using pivot table
text
Body
copied!<p>Table structure:</p> <p><img src="https://i.stack.imgur.com/k5WeW.png" alt="enter image description here"></p> <p>This is the data for which i want to use pivot table</p> <p><img src="https://i.stack.imgur.com/kJAri.png" alt="enter image description here"></p> <p>i want to display the result as</p> <p>Teacher Activity [2013-11-22] and so on The date field will display the count of the attendance.</p> <p>Below is the code which i am using, result is coming as 0 under every date, which is not correct:</p> <pre><code> DECLARE @temp AS TABLE(dates datetime) ;with cte (datelist, maxdate) as ( select DATEADD(dd,-3,GETDATE()), GETDATE() union all select dateadd(dd, 1, datelist), maxdate from cte where datelist < maxdate ) INSERT INTO @temp SELECT c.datelist FROM cte c DECLARE @listdate nvarchar(MAX) SELECT @listdate = (select(STUFF( (SELECT ', ' + QUOTENAME(convert(CHAR(10), dates, 120)) from @temp ORDER BY dates asc FOR XML PATH('')),1, 1, ''))) print @listdate exec('(SELECT * INTO ##temptable from (select distinct teachername as Teacher,activityname AS Activity,memberid as attendance, QUOTENAME(convert(CHAR(10), attendancedate, 120)) AS DATES from tbteachertimelog inner join tbteacher on teacherid = tbteacher.id inner join tbactivity on tbactivity.id = tbteachertimelog.activityid left join tbattendance on tbattendance.activityid = tbteachertimelog.activityid and convert(CHAR(10), tbattendance.attendancedate, 120) = convert(CHAR(10), tbteachertimelog.date, 120) group by teachername,activityname,memberid, attendancedate ) p PIVOT ( count(attendance) FOR DATES IN ('+@listdate+')) AS pvt ) ') alter table ##temptable add TotalStudents int,meetings int,total64 int update ##temptable set TotalStudents = (SELECT SUM(memcount) FROM (select count(distinct memberid) as memcount from tbteachertimelog inner join tbattendance on tbattendance.activityid = tbteachertimelog.activityid and convert(CHAR(10), tbattendance.attendancedate, 120) = convert(CHAR(10), tbteachertimelog.date, 120) where --teacherid = ##temptable.teacherid and tbteachertimelog.activityid = ##temptable.activityid --and tbattendance.attendancedate >= dateadd(dd,-7,getdate()) and tbattendance.attendancedate <= getdate() group by convert(CHAR(10), tbattendance.attendancedate, 120)) x) update ##temptable set meetings = (select count(distinct convert(CHAR(10), tbattendance.attendancedate, 120)) as dayscount from tbteachertimelog inner join tbattendance on tbattendance.activityid = tbteachertimelog.activityid and convert(CHAR(10), tbattendance.attendancedate, 120) = convert(CHAR(10), tbteachertimelog.date, 120) where teacherid = ##temptable.teacherid and tbteachertimelog.activityid = ##temptable.activityid) select * from ##temptable drop table ##temptable </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload