Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i m generating a report of the monthly attendance of the students using the cross table query ..i have a table having fields as User_Id,Date,Time and status
the code which i tried shows attendance seperately for each date for the same User_id


User_Id 1 2 3 4
1121 P null null null
1121 null P null null
1121 null null P null

how do i solve it???

What I have tried:

select [User_Id],[1],[2],[3],[4] from
(select [User_Id], day([Date])as date,Time,Status from [first_db].[dbo].[MarkA]) ma
pivot
(
max[status]
for Date in ([1],[2],[3],[4])
)as pvt
order by
pvt.User_Id
Posted
Updated 18-Feb-18 17:35pm
Comments
OriginalGriff 18-Feb-18 10:58am    
How do you solve what?
You haven't asked a question, and we only get exactly what you type to work from.
We have no idea what you are trying to achieve, ort how that does or doesn't fill your expectations.
Member 13674721 18-Feb-18 11:44am    
i just want to know what is wrong with this query
i want to generate the following output in sql :
User_Id 2018-02-01 2018-02-02 2018-02-03
1001 P P A
1002 P P P
1003 A P P

Instead i am getting this output:
User_Id 2018-02-01 2018-02-02 2018-02-03
1001 P Null Null
1001 null P Null
1002 P Nulll Null
1002 null P null
itsmypassion 18-Feb-18 23:42pm    
Please share sample data.

1 solution

SELECT [User_Id],
       MAX([1]) AS [1],
       MAX([2]) AS [2],
       MAX([3]) AS [3],
       MAX([4]) AS [4] 
  FROM [first_db].[dbo].[MarkA]
   PIVOT
       (
         MAX(Status) FOR Date in ([1],[2],[3],[4])
       )as pvt
  GROUP BY [User_Id]  ORDER BY [User_Id]
 
Share this answer
 
Comments
Member 13674721 19-Feb-18 6:00am    
Thanks for the solution.But instead of displaying 1,2,3,4 as my column headings i want the date to be present as the column heading for eg.2018-02-01,2018-02-02,2018-02-03
.How do I do this?
Santosh kumar Pithani 19-Feb-18 7:29am    
you have give allies MAX([1]) AS '2018-02-01' instead of [1]..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900