Please suggest me ideas on the below requirement.

My requirement is to create a dynamic Sankey diagram which gets generated from the SQL Server Data. For example, when user clicks a dropdown, the dropdown should be passed as input to the SQL Server Database and would return data with which the Sankey chart would be built. I researched and found that Sankey's can be generated from CSV data and Json data. I am thinking to extract the data from SQL server and convert it into Json and then giving this Json as input for generating the Sankey diagram. I am trying to build it in PHP or asp.net and use the javascript's d3 plugin for Sankey.

Please let me know if this is the only way or there are any other ways that I can program this Sankey from SQL. I appreciate your time and effort for reading the post and thanks in advance for helping.

Thanks and regards, Sathappan Ramanathan

JS: (limits only IE)

// STEP 1: Initialize a new ActiveXObject for the SQL Server connection.
var connection = new ActiveXObject("ADODB.Connection") ;

// Your connection string: and this is the reason why you shouldn't do this
// in live website, "everyone has access to this connection string".
// replace the values within "<>" to your values.
var your_connection_string = "Data Source=<SQL Server (IP or host)>;Initial Catalog=<your catalog (a.k.a Database)>;User ID=<your username>;Password=<your password>;Provider=SQLOLEDB";

// STEP 2: Now open the connection using the connection string above.
connection.Open(your_connection_string);

// STEP 3: Initialize a new activeX object, this time for a recordset,
// so we can read data from database.
var rs = new ActiveXObject("ADODB.Recordset");

// STEP 4: Manipulate your data the way you want.
rs.Open("SELECT * FROM <your table>", connection);
rs.MoveFirst
while(!rs.eof)
{
document.write(rs.fields(1));
rs.movenext;
}

// STEP 5: be nice and Finalize the recordset and the connection.
rs.close;
connection.close;

working connection string (test on SQL 2008R2, 2014 integrated security) :

 var connectionstring1 = "Data Source=servername\\instancename;Initial Catalog=DBname;Integrated Security=SSPI;Provider=SQLOLEDB";

more