Saturday 17 August 2013

How to Use pivot function in sql server and in Lightswitch?

How to Use pivot function in sql server and then in Lightswitch?

In this post you will know that how can you use sql server pivot function in visual studio lightswitch. Mainly pivot function is used to convert rows into columns. Here is an example of school time table with respect to a particular staff. With this example I will try to explain this concept. We will insert data row wise and they represent as column wise. 
Steps:
1. Insert data row wise from lightswitch screen

 Sql Server Entries 


2.  Create a view that will show these entries as column wise

ALTER view [dbo].[vwTimeTable] as
select pvt.*
            from (
            select Day = Day,
            TimeID,StaffMa.TecID,
            StaffName = StaffMa.Name,
            Description= Remarks,
            [Time] = Convert(varchar(10), T1.StartTime) + '  to ' +Convert(Varchar(10), T1.EndTime)
 from tbTimeTable) P
pivot
(
Max(P.Time) for P.Day in ([Sunday],[Monday],[Tuseday],[Wednesday],[Thursday],[Friday],[Saturday])
)
as pvt

3. Out Put is like as 

3 comments:

Unknown said...

Hi
I could not find the two images RatingBlank.png and RatingSelected.png? I create .png files and continued but it fails on the render. Could I please have these two .png images or a sample of your code where you use. I am on vs2013 update 4.

Unknown said...

Hi
I could not find the two images RatingBlank.png and RatingSelected.png? I create .png files and continued but it fails on the render. Could I please have these two .png images or a sample of your code where you use. I am on vs2013 update 4.

Unknown said...

Pieter: I think you are talking about http://lightswitch.harshgarg.com/2014/12/customize-lightswitch-htmlclient.html