Last February, I had come over to Atlanta, Georgia from India for a new project and had been quite overwhelmed by the new way of life, the work and the country that I wasn't able to do justice to this blog. I apologize for those who had been following this mini series of scenarios. Hope I can get some time now to revive this.
Here goes the scenario:
Its been a long time since I had used this medium of communication. Without getting into the excuses part of the dormancy I will get on with the question...
Here is the scenario. Say, I am in a business consulting company and I am given a table EmployeeCustomerOrders.
The table can be generated from the orders table in the Northwind database using this query.
Thinking in sets:
The thought process that goes into set- based programming is slightly different from writing procedural code.
Say, I have 2 tables -one holding order header information and the other holding the details of the products ordered. I want to get the items ordered for all the orders:
This time it's a pretty interesting scenario to solve. I have a table called the distance_tbl holding distance between different cities.
Here is the table definition:
create table #distance_tbl (city1 char(1), city2 char(1), distance int)
And the sample data:
I don't really know why I am starting this series, if it will be really useful for anyone and whether I will be able to keep it running for long. But, its here because I am convinced that sometimes the most obvious escapes our mind. And as someone rightly said "common sense is not so common".
I just intend to illustrate few common issues(one per post) where we try to come up with complicate queries and realise, in the end, that we have a painfully obvious alternative. But, the question is - Is it obvious and is it really an alternative? Read ahead...
Say,I have a requirement. My business demands an auto-generated key (or an identity) for a table called CharTbl. But, it needs to be a character identity column. The sequence generation should be as given in the figure below (partial result displayed).
How would I go about creating the table definition?
This post is written with reference to the post Pivot Query Generator for SQL Server 2005. Please read through the post to get the context and the data setup before you read any further.
Now, that you have read through the other post, you will realise that in SQL Server 2005, we had the PIVOT operator to generate the crosstab reports. But in case of SQL Server 2000, we will have to use an indirect approach to generate the cross-tab. Here is the SQL Server 2000 counter part of the Pivot Query Generator of SQL Server 2005.
Posts
All Comments
