We can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table in SQL Server. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

Before PIVOT

After PIVOT

A simple PIVOT sample in SQL Server.Here we are going to demonstrate a very simple PIVOT sample without any complexity. We are having a table named Accounts and it containing customer name and their deposit with denominations.

Table Structure For showing simple PIVOT sample

CREATE TABLE Accounts(Customer VARCHAR(25), Denomination VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('John','10 $',2)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('John','50 $',6)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('John','100 $',1)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Ram','10 $',4)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Ram','50 $',3)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Ram','100 $',11)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('KATE','10 $',20)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('KATE','50 $',12)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('KATE','100 $',2)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Eby','10 $',0)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Eby','50 $',5)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Eby','100 $',5) 

In order to PIVOT above mentioned table we can use below script. The result should be as Customer name with all denomination will be coming as columns with qty as values for each column.

SELECT * FROM Accounts
PIVOT (SUM(QTY) For Denomination IN ([10 $],[50 $],[100 $])) AS Total

Dynamic Query to PIVOT table for dynamic columns. In the above example we are using a simple structured table and then PIVOT with denomination values. This can be achieved only when we are having denomination values as static. Suppose this denomination values are dynamic (Each country having different denomination like $,EUR, IND etc..), we need to create a dynamic query to PIVOT above table. Suppose we are having different table for getting Denomination values and we are going to take Denomination values from this table at run time as dynamic.

CREATE TABLE Denomination(Value VARCHAR(25))
GO
INSERT INTO Denomination(Value)
VALUES('10 $')
INSERT INTO Denomination(Value)
VALUES('50 $')
INSERT INTO Denomination(Value)
VALUES('100 $')

First of all, we need to get dynamic columns names from the above table. After that we can create a dynamic query with these columns.

Declare @ColumnNames VARCHAR(100);
SELECT @ColumnNames = COALESCE(@ColumnNames+ ',','') +
'['+ Cast(Value AS VARCHAR(50)) +']' FROM Denomination cust
PRINT @ColumnNames
DECLARE @DynamicQuery Varchar(MAX);
SET @DynamicQuery = '
SELECT * FROM Accounts
PIVOT (SUM(QTY) For Denomination IN (' + @ColumnNames + ')) AS Total'
EXEC (@DynamicQuery);

JavaScript is extremely popular language and already has implementations for server-side programming. For example Node.js  - a server-side JavaScript environment that utilizes Goggle's V8 JavaScript Engine.

I am an ASP.NET developer and want to use JavaScript in the ASP.NET environment. I found Javascript .NET project, that bring Google's V8 to the .NET world and I started with a simple task - to write a custom ASP.NET HTTP Handler using JavaScript. (I had done similar task with IronPython before).  If you interested in ASP.NET 4, we recommend you to try HostForLife.eu. We will give the best service at an affordable price. You can start with our lowest price € 3.00/month to host your ASP.NET 4 site.

Setup requirements

1. I want *.js file to be processed on server (like *.aspx or *.ashx)
2. I want to access server side objects (such HttpContext, HttpRequest and HttpResponse) from javascript code.

Implementation

First of all, download Javascript .NET and add the reference to Noesis.Javascript.dll. It embeds Google's V8 and contains an API required to run JavaScript code.

Setup custom HTTP Handler in web.config. It will handle any request of *.js file under App folder. I configure it this way to allow other javascript files (not under App folder) to be processed as static content for being used in browser.

Next step - create App folder and  HelloWorld.js file:

Write the single line code:

At this moment I expect this code run on server and produce simple 'Hello World!' html.
But to make it works I have to implement JavaScriptHttpHandlerFactory - the core of all this.

JavaScriptHttpHandlerFactory

Implementation is listed below:

using System.IO;
using System.Web;
using Noesis.Javascript;

namespace Web
{
    public class JavaScriptHttpHandlerFactory : IHttpHandlerFactory
    {
        public IHttpHandler GetHandler(HttpContext context,
                   string requestType, string url, string pathTranslated)
        {
            return new JavaScriptHttpHandler(pathTranslated);
        }

        public void ReleaseHandler(IHttpHandler handler)
        {
        }
    }

    public class JavaScriptHttpHandler : IHttpHandler
    {
        private readonly string pathTranslated;

        public JavaScriptHttpHandler(string pathTranslated)
        {
            this.pathTranslated = pathTranslated;
        }

        public void ProcessRequest(HttpContext context)
        {
            var scriptCode = File.ReadAllText(pathTranslated);

            using (var jsContext = new JavascriptContext())
            {
                jsContext.SetParameter("context", context);
                jsContext.SetParameter("request", context.Request);
                jsContext.SetParameter("response", context.Response);

                try
                {
                    jsContext.Run(scriptCode);
                }
                catch (JavascriptException ex)
                {
                    throw new HttpParseException(ex.Message, ex,
                      pathTranslated, scriptCode, ex.Line);
                }
            }
        }

        public bool IsReusable
        {
            get { return false; }
        }
    }
}

As you may see, implementation of JavaScriptHttpHandlerFactory is pretty simple. Read the js file, create JavascriptContext, setup context parameters and finally execute.

Now when you run HelloWorld.js you get "Hello World!" in browser:

Error handling

But all of this worth nothing if we are not able to debug javascript easily.
Javascript.NET allows us to handle javascript errors and even points to source code line where error was occurred.

I used this feature to expose a javascript error in convenient format of "yellow screen of death":

Conclusion

Integration of JavaScript into ASP.NET environment is possible and not so hard

BTW, Javascript.NET is not only technology allows it. There is IronJS wich runs javascript over DLR. May be in one next posts will play with it.