Using Cross Apply to convert comma separated fields to row

  SELECT A.mapgroupno,
     Split.a.value('.', 'VARCHAR(100)') AS String
 FROM  (SELECT MMapmodulelocation.mapgroupno,
         CAST ('<M>' + REPLACE(MMapmodulelocation.mapdnwlperson, ',', '</M><M>') + '</M>' AS XML) AS String
     FROM  MMapmodulelocation) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

Alert Session Time out in ASP.NET

Introduction

One of the requirements in my project was to warn users about the session expiry. Though it looks like a simple requirement for the end users, it is not the case for developers and designers. We need to deal with lot of scenarios in the real time application. What is the best way to achieve the objective? Some of the challenges would be like:

1. Session is a sliding expiry value. It gets extended every time there is a post back.
2. There are multiple ways that you can handle this scenario and each of them has its own technical challenges.

Approaches

The following section will try to cover few of the approaches to handle session expiry.

1. Provide a Simple Alert

In this approach, the user will be provided with a simple warning message, based on a pre-defined time interval.

<script language="javascript" type="text/javascript">
       var sessionTimeoutWarning = 
 "<%= System.Configuration.ConfigurationSettings.AppSettings
 ["SessionWarning"].ToString()%>";
        var sessionTimeout = "<%= Session.Timeout %>";

        var sTimeout = parseInt(sessionTimeoutWarning) * 60 * 1000;
        setTimeout('SessionWarning()', sTimeout);

        function SessionWarning() {
var message = "Your session will expire in another " + 
 (parseInt(sessionTimeout) - parseInt(sessionTimeoutWarning)) + 
 " mins! Please Save the data before the session expires";
alert(message);
        }
</script>

• sessionTimeoutWarning: is a predefined value in the application configuration. Say 18 minutes.
• sessionTimeout: holds the session timeout interval. Say 20 minutes. In case the user does not do any post back on the page for about 18 minutes, he will be warned about the session expiry.

2. Provide a Simple Alert and Then Redirect the User to Home Page or Login Page

  <script language="javascript" type="text/javascript">
        var sessionTimeoutWarning = 
 "<%= System.Configuration.ConfigurationSettings.AppSettings
 ["SessionWarning"].ToString()%>";
        var sessionTimeout = "<%= Session.Timeout %>";
        var timeOnPageLoad = new Date();
 
        //For warning
        setTimeout('SessionWarning()', parseInt(sessionTimeoutWarning) * 60 * 1000);
        //To redirect to the welcome page
        setTimeout('RedirectToWelcomePage()',parseInt(sessionTimeout) * 60 * 1000);

        //Session Warning
        function SessionWarning() {
            //minutes left for expiry
            var minutesForExpiry =  (parseInt(sessionTimeout) - 
    parseInt(sessionTimeoutWarning));
            var message = "Your session will expire in another " + minutesForExpiry + 
   " mins! Please Save the data before the session expires";
            alert(message);
            var currentTime = new Date();
            //time for expiry
            var timeForExpiry = timeOnPageLoad.setMinutes(timeOnPageLoad.getMinutes() 
    + parseInt(sessionTimeout)); 

            //Current time is greater than the expiry time
            if(Date.parse(currentTime) > timeForExpiry)
            {
                alert("Session expired. You will be redirected to welcome page");
                window.location = "../Welcome.aspx";
            }
        }

        //Session timeout
        function RedirectToWelcomePage(){
            alert("Session expired. You will be redirected to welcome page");
            window.location = "../Welcome.aspx";
        }
  </script> 

In this approach, the user will be warned about the session timeout. If user does not save or do any post back, he would be redirected to the login or home page, once the session interval time expires.

3. Extend User Session

 <script language="javascript" type="text/javascript">
        var sessionTimeoutWarning = 
 "<%= System.Configuration.ConfigurationSettings.AppSettings
 ["SessionWarning"].ToString()%>";
        var sessionTimeout = "<%= Session.Timeout %>";
        var timeOnPageLoad = new Date();
        var sessionWarningTimer = null;
        var redirectToWelcomePageTimer = null;
        //For warning
        var sessionWarningTimer = setTimeout('SessionWarning()', 
    parseInt(sessionTimeoutWarning) * 60 * 1000);
        //To redirect to the welcome page
        var redirectToWelcomePageTimer = setTimeout('RedirectToWelcomePage()',
     parseInt(sessionTimeout) * 60 * 1000);

        //Session Warning
        function SessionWarning() {
            //minutes left for expiry
            var minutesForExpiry =  (parseInt(sessionTimeout) - 
     parseInt(sessionTimeoutWarning));
            var message = "Your session will expire in another " + 
  minutesForExpiry + " mins. Do you want to extend the session?";

            //Confirm the user if he wants to extend the session
            answer = confirm(message);

            //if yes, extend the session.
            if(answer)
            {
                var img = new Image(1, 1);
                img.src = 'KeepAlive.aspx?date=' + escape(new Date());

                //Clear the RedirectToWelcomePage method
                if (redirectToWelcomePageTimer != null) {
                    clearTimeout(redirectToWelcomePageTimer);
                }
           //reset the time on page load
                timeOnPageLoad =  new Date();
                sessionWarningTimer = setTimeout('SessionWarning()', 
    parseInt(sessionTimeoutWarning) * 60 * 1000);
                //To redirect to the welcome page
                redirectToWelcomePageTimer = setTimeout
  ('RedirectToWelcomePage()',parseInt(sessionTimeout) * 60 * 1000);
            }

            //*************************
            //Even after clicking ok(extending session) or cancel button, 
    //if the session time is over. Then exit the session.
            var currentTime = new Date();
            //time for expiry
            var timeForExpiry = timeOnPageLoad.setMinutes(timeOnPageLoad.getMinutes() + 
    parseInt(sessionTimeout)); 

            //Current time is greater than the expiry time
            if(Date.parse(currentTime) > timeForExpiry)
            {
                alert("Session expired. You will be redirected to welcome page");
                window.location = "../Welcome.aspx";
            }
            //**************************
        }

        //Session timeout
        function RedirectToWelcomePage(){
            alert("Session expired. You will be redirected to welcome page");
            window.location = "../Welcome.aspx";
        }
</script>   

In this approach, the user will be warned about the session timeout and provides the ability to extend user session. If the user confirms to extend the session, it gets extended. If user confirms after the session expiry timeout limit, even then the user will be logged out. Following lines of code are used to extend the user session. Where 'KeepAlive.aspx is a dummy page in the website.

var img = new Image(1, 1); 
img.src = 'KeepAlive.aspx?date=' + escape(new Date()); 

Note: In all the above scenarios, I am assuming SetTimeout method and session related variables will be reset whenever there is a post back. This may not work 100%, when there could be partial rendering and the SetTimeoutmethod and session related variables may not be reset. All files are in the Samples folder.

UNPIVOT Multiple Columns

Unlike PIVOT which de-normalizes the data, UNPIVOT is more helpful in getting your data back to a more normalized form.

UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

For a single column UNPIVOT examples and for more on PIVOT and UNPIVOT you can read from Microsoft Books Online - Using PIVOT and UNPIVOT

In this article I’ll try to show you how to UNPIVOT multiple columns in a single query. There is simple trick to do that, if you are familiar with the UNPIVOT operator you will find it easy to understand.

First lets create some sample data :

-- Create sample table CREATE TABLE Suppliers (Id INT, Product VARCHAR(500), Supplier1 VARCHAR(500), Supplier2 VARCHAR(500), Supplier3 VARCHAR(500), City1 VARCHAR(500), City2 VARCHAR(500), City3 VARCHAR(500) ) GO -- Load Sample data INSERT INTO Suppliers SELECT 1, 'Car', 'Tata Motors', 'Bajaj', 'Mahindra', 'Jamshedpur','Pune','Mumbai' UNION ALL SELECT 2, 'Bike', 'Bajaj', 'Hero Honda', 'Suzuki', 'Pune', 'New Delhi', 'Chandigarh' UNION ALL SELECT 3, 'Cycle', 'Hercules', 'Hero', 'Atlas', 'Mumbai', 'Banglaore', 'Pune' GO -- Test sample data SELECT Id, Product, Supplier1, Supplier2, Supplier3, City1, City2, City3 FROM Suppliers GO

Here is how our sample table looks like :

Id	Product	Supplier1	Supplier2	Supplier3	City1	City2	City3
1	Car	TataMotors	Bajaj	Mahindra	Jamshedpur	Pune	Mumbai
2	Bike	Bajaj	Hero Honda	Suzuki	Pune	New Delhi	Chandigarh
3	Cycle	Hercules	Hero	Atlas	Mumbai	Bangalore	Pune

And as you can see here we have 2 columns which needs to be UNPIVOT, first is Supplier and second is there respective Cities.  If it was single column to be UNPIVOTed it could have been a straight forward task.  Thing is you can write a query with 2 UNPIVOT operators, but question is how to JOIN two UNPIVOT sub queries correctly so we will get the each supplier with its respective city next to it in second column?

As I said earlier there is simple trick to do that.  See the following query and its WHERE condition :

SELECT Id,     Product,     ROW_NUMBER()OVER(Partition By Id Order By Suppliers) as SuppId,     SupplierName,     CityName FROM ( SELECT Id, Product, Supplier1, Supplier2, Supplier3, City1, City2, City3 FROM Suppliers ) Main UNPIVOT ( SupplierName FOR Suppliers IN (Supplier1, Supplier2, Supplier3) ) Sup UNPIVOT ( CityName For Cities IN (City1, City2, City3) ) Ct WHERE RIGHT(Suppliers,1) =  RIGHT(Cities,1)

And here is the Output of the query : 

Id	Product	SuppId	SupplierName	CityName
1	Car	1	Tata Motors	Jamshedpur
1	Car	2	Bajaj	Pune
1	Car	3	Mahindra	Mumbai
2	Bike	1	Bajaj	Pune
2	Bike	2	Hero Honda	New Delhi
2	Bike	3	Suzuki	Chandigarh
3	Cycle	1	Hercules	Mumbai
3	Cycle	2	Hero	Bangalore
3	Cycle	3	Atlas	Pune

So the trick was this where condition -

WHERE RIGHT(Suppliers,1) =  RIGHT(Cities,1)

There are other ways also to UNPIVOT the multiple columns, may be some other time I’ll post them too. 

Source of inspiration for this post is following thread on MSDN forums -http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/54626179-ee9f-4db7-a31e-c02104bdd6beAt 

At the end of it, OP also come up with his own solution he got from SQLTeam’s forums.