Convert Row To Column In SQL Server 2005

April 2, 2009

Let us suppose that you have a table like below

col1 | col2 | col3

——–*———*———-

Value 1 | Value 2 | Value 3

And change it to one that looks like this:

Name | Value

—–*———

col1 | Value 1

—–*———

col2 | Value 2

—–*———

col3 | Value 3

DECLARE @Table Table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)
INSERT INTO @TABLE VALUES (‘Value 1’, ‘Value 2’, ‘Value 3’)
INSERT INTO @TABLE VALUES (‘Value 4’, ‘Value 5’, ‘Value 6’)
INSERT INTO @TABLE VALUES (‘Value 7’, ‘Value 8’, ‘Value 9’)

SELECT col, colval
FROM
(SELECT col1, col2, col3FROM @TABLE) p
UNPIVOT
(ColVal FOR Col IN
(col1, col2, col3)
) AS unpvt

[Note: To execute above sql query, one important thing you have to do in your database i.e you have to execute
EXEC sp_dbcmptlevel databse_name, 90; — for sql server 2005
EXEC sp_dbcmptlevel qdevdb, 80; –for sql server 2000]

Advertisements

LinQ Best Book

April 2, 2009

linqbook

“This SQL Transaction has completed; it is no longer usable.” errors

March 27, 2009

See the below link

Michael Freidgeim’s Blog

Want to know is Linked in Help?

March 5, 2009

List of useful blogs and URLs

March 4, 2009

Microsoft .NET Frmaework 3.5

March 4, 2009

microsoftnetframework35

SQL Server Stored Procedures – Returning Multiple Tables to ADO.NET

December 29, 2008

how to use Multiple ResultSets with the ADO.NET SqlDataReader class. In John’s article he illustrates how you can execute multiple SQL select statements in batch and access all of the results of each SQL select statement with SqlDataReader.NextResult(). The MSDN library documentation for SqlDataReader.NextResult() indicates that it “advances the data reader to the next result, when reading the results of batch Transact-SQL statements.” You can also utilize SqlDataReader.NextResult() with SQL Server stored procedures.

In addition, you can access multiple ResultSets with SqlDataAdapter.Fill(), which is what this blog focuses on.

If a single stored procedure issues several SELECT statements, they are all returned to the caller by default. If we convert John’s example of running 4 queries in batch against the Northwind database to a stored procedure, we now have:

/* Test stored procedure. Returns 4 ResultSets */
CREATE PROCEDURE spSomeStoredProcedure AS
BEGIN

SELECT CategoryName FROM Categories ORDER BY CategoryName
SELECT Top 10 CompanyName FROM Customers ORDER BY CompanyName
SELECT LastName FROM Employees ORDER BY LastName

SELECT Top 10 ProductName FROM Products ORDER BY ProductName

END

GO

Using SqlDataAdapter.Fill() to access all of your returned tables in ADO.NET is really easy:

SqlConnection conn = new SqlConnection(connection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(“exec spSomeStoredProc”, conn);
adapter.Fill(dataset);

// dataset.Tables[0] – refers to resultset obtained from first SQL query
// in stored procedure. dataset.Tables[1] – refers to resultset obtained

// from second SQL query. Etc.

The ordinal position of each DataTable in dataset.Tables matches the order that the queries are executed within the stored procedure. This technique can and should be used to minimize network roundtrips between your application server and database server (in addition to minimizing all of the marshalling and additional overhead required per remote database query execution).

Differrence between Convert.To…() and .To…()

December 4, 2008

Mostly problems comes while converting string data.
Always use Convert.ToString(strString) in place of strString.ToString() if your strString may contain NULL.
Because when you use strString.ToString(), it gives error if your strString contains NULL.

Adding “Select One” inside dropdown list

December 4, 2008

Q. How to add “Select One” item inside dropdown list while populating dropdownlist from database?
Q. First item selection problem – no event fire on select index changed but it works for second item.

Answer:
Below is the code to add “Select One” option as first item inside dropdown list.
Here BindDropDownList1() is the function to bind dropdown list from database.
private void BindDropDownList1()
{
string strConn = “server=(local);uid=UID;pwd=PWD;database=Northwind”;
string MySQL = “Select LastName from Employees”;
SQLConnection MyConn =new SQLConnection(strConn);
SQLDataReader objDR ;
SQLCommand Cmd =new SQLCommand(MySQL, MyConn);
MyConn.Open();
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection);
DropDownList.DataSource = objDR;
DropDownList.DataBind();
DropDownList.Items.Insert(“0”,new ListItem(“Select One”,”0″));
}

Now your dropdown list looks like:
dropdownlistboxwithselectoneoption

[Note: the last line DropDownList.Items.Insert(“0”,new ListItem(“Select One”,”0″)); add “Select One” item in your dropdown list and its index is “0”.
Now when the Page Load and dropdown list fill with all the data from database as well as with “Select One” item and “Select One” is selected as your first item.
Now the event is also fire on select index changed for the first item that comes from your database.
]

DropDownList losing data on page submission or on page postback

December 4, 2008

Q.”Why doesn’t my DropDownList keep it’s Selection?”
Q.”Why is the selectedindex for my (ASP.Net control) always turning up a -1?”

Answer:

The problem occurs due to not checking of Page Post Back on Page Load

private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{
BindDropDownList1();
}
}

[Note: you may be missing if(!IsPostBack) line on page load.
!Page.IsPostBack read as Not Page is Page Back prevents on each subsequent loading of the page, the control doesn’t go through the redundant action of re-loading the control. It loads it only once, at the initial page load and the selection which was made by the end user is maintained throughout the post back.
BindDropDownList1() is the method created by You to fill your DropDownList box.]
Below is the BindDopDownList1 method that you have written in your page or in any other class.

private void BindDropDownList1()
{
string strConn = “server=(local);uid=UID;pwd=PWD;database=Northwind”;
string MySQL = “Select LastName from Employees”;
SQLConnection MyConn =new SQLConnection(strConn);
SQLDataReader objDR ;
SQLCommand Cmd =new SQLCommand(MySQL, MyConn);
MyConn.Open();
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection);
DropDownList.DataSource = objDR;
DropDownList.DataBind();
}