Archive for June, 2007

Cascading style sheets (CSS) are used in conjunction with JavaScript and HTML to make it Dynamic or DHTML.
It’s a normal procedure for any UI (User Interface) developer to run into this situation while developing web pages where he has to do away with the appended px to the position style property.
I am presenting an example with positioning an image on a web page; hence sharing a little tip.

The code:
            <TITLE> Getting Rid of the px </TITLE>
<SCRIPT type=”text/javascript”>
function ImgMove()
 document.getElementById(“ImgDiv”).style.left = parseInt(document.getElementById(“ImgDiv”).style.left)+5;
 // This line would give an error
 //document.getElementById(“ImgDiv”).style.left = document.getElementById(“ImgDiv”).style.left+5;
            <H1> Displacing a picture Right </H1>
            <P> Every click of the button displaces it 5 pixels to the Right </P>
            <DIV id=”ImgDiv” style=”position:absolute;top:150;left:100;visibility:visible”>
            <img src=”http://www.geocities.com/dotnetolympians/logo.jpg“>
            <button id=”ImgButton” onclick=”ImgMove()”> Click To Move Image </button>

The trick lies in this line of code:
            document.getElementById(“ImgDiv”).style.left = parseInt(document.getElementById(“ImgDiv”).style.left)+5;

This is mostly written as:
            document.getElementById(“ImgDiv”).style.left = document.getElementById(“ImgDiv”).style.left+5;

The value returned by the style.left property (CSS positioning property) of the browser appends a px to the right. In this case for the first run you will get 100px instead of 100. Same is the case with the rest of the positioning style properties style.top, style.right, style.bottom, style.right.

Here is a running example.
Download the code.


Read Full Post »

This is one of the oldest topics that one could write on but I am trying to just relight the flame. Open to comments from all.

The table:

I am using the Products table present in Northwind Database to continue with this example. If you don’t have the above database you can download it from here:

Products Table structure:

Column Name Column Type
ProductID                    int (IDENTITY)
ProductName               nvarchar(40)
SupplierID                    int
CategoryID                  int
QuantityPerUnit            nvarchar(20)
UnitPrice                      money
UnitsInStock                smallint
UnitsOnOrder              smallint
ReorderLevel               smallint
Discontinued                 bit

Initial data in table:
Select CategoryId, ProductName  From Products

CategoryId ProductName
1          Chai
1          Chang
2          Aniseed Syrup
2          Chef Anton’s Cajun Seasoning
2          Chef Anton’s Gumbo Mix
1          Lakkalikööri
2          Original Frankfurter grüne Soße
            (77 Rows returned)

The Requirement to Display data:

CategoryId ProductsList
1                      Chai,Chang,Guaraná Fantástica,Sasquatch …
2                      Aniseed Syrup,Chef Anton’s Cajun Se …
3                      Pavlova,Teatime Chocolate Biscuits, …
4                      Queso Cabrales,Queso Manchego La Pa …
5                      Gustaf’s Knäckebröd,Tunnbröd,Singap…
6                      Mishi Kobe Niku,Alice Mutton,…
7                      Uncle Bob’s Organic Dried Pears,Tof…
8                      Ikura,Konbu,Carnarvon Tigers,Nord-O…

The solution I like the most:

I like using this user defined function named fn_GetProductNameList.

CREATE FUNCTION dbo.fn_GetProductNameList
            @CatId                        int
RETURNS VarChar(8000)
            Declare @buffer VarChar(8000)
            Select   @buffer = IsNull(@buffer + ‘,’, ”) + ProductName
                        From    Products
                        Where CategoryId = @CatId
            RETURN @buffer


Select Distinct CategoryId , dbo.fn_GetProductNameList (CategoryId)
From Products

To better understand this approach and how this select statement work you need to go through this knowledge base article.

So to produce the values in a column as a comma separated values from the same table you can have the following multiple ways as well.

1.      Another User Defined function: (fn_GetProductNameList2)

CREATE FUNCTION dbo.fn_GetProductNameList2
            @CatId Int
RETURNS VarChar(8000)
            Declare            @t Table
                        p          VarChar(40)
            Declare            @r VarChar(8000)
            Set @r = SPACE(0)
            Insert @t ( p ) select ProductName From Products
                        Where CategoryId = @CatId
            IF @@ROWCOUNT > 0
                        Update @t
                                                SET @r = @r + p + ‘,’


Select CategoryId, dbo.fn_GetProductNameList2(CategoryId)
From Products Group By CategoryId

These functions above fn_GetProductNameList and fn_GetProductNameList2 are widely used but are not recommended approach because of the fact that they rely on the physical implementation. Any changes in the indexing, statistics or else can make them unreliable.

2.      The Cursor:
CREATE FUNCTION dbo.fn_GetProductNameList3 
            @CatId                        Int
RETURNS VarChar(4000)
            Declare @ProductName Varchar(4000)
            Set @ProductName = ”
            Declare @CurVar        Varchar(255)  
            Declare Cur_ProductName Cursor
                        Select   Distinct ProductName
                        From    Products
                        Where  CategoryId = @CatId
            Open Cur_ProductName
            Fetch Cur_ProductName Into @CurVar
            While (@@Fetch_Status <> -1)
                        Set @ProductName = @ProductName + @CurVar + ‘, ‘
            Fetch Next From Cur_ProductName Into @CurVar
            Close Cur_ProductName
            Deallocate Cur_ProductName
            If(LTrim(RTrim(@ProductName)) <> ”)
                        Return Substring(LTrim(RTrim(@ProductName)), 0, Len(RTrim(@ProductName)) -1)
            Return LTrim(RTrim(@ProductName))


Select CategoryId, dbo.fn_GetProductNameList3(CategoryId)
From Products Group By CategoryId

This user defined function contains a cursor to do the magic. Its more reliable but the drawbacks of cursor on performance are know to all so they should be avoided and loops used instead.

3.      Loop inside a user defined function: (Linda Wierzbecki)
CREATE FUNCTION dbo.fn_GetProductNameList4()
RETURNS @tempProducts TABLE
            CategoryId                   Int,
            Product                        VarChar           (40),
            ListProductNames        VarChar           (8000)
            Insert @tempProducts (CategoryId, Product, ListProductNames)
            Select CategoryId, MIN(ProductName),  MIN(ProductName)
                        From Products
                        Group By CategoryId
            While ( Select COUNT(Product) From @tempProducts ) > 0
            Update TP
                        Set ListProductNames = ListProductNames + COALESCE(
                                    ( Select ‘, ‘ + MIN( ProductName )
                                                            From Products
                                                Where Products.CategoryId = TP.CategoryId
                                                                        AND Products.ProductName > TP.Product), ”),
                                        Product = ( Select MIN(ProductName)
                                                            From Products
                                                                        Where Products.CategoryId = TP.CategoryId
                                                                        AND Products.ProductName > TP.Product )
            From @tempProducts TP


Select CategoryId, ListProductNames AS Products From dbo.fn_GetProductNameList4()

This approach is also widely used.

4.      Recursive User Defined Function:
CREATE FUNCTION dbo.fn_GetProductNameList5
            @CatId Int,
            @i Int
RETURNS VarChar    (8000)
            Declare @r Varchar     (8000)
            Declare @l Varchar      (8000)
        Select @i = @i – 1,  @r = ProductName + ‘, ‘
                        From Products p1
                        Where CategoryId = @CatId
                        AND @i = ( Select COUNT(*) From Products p2
                                                Where p2.CategoryId = p1.CategoryId
                                                            AND p2.ProductName <= p1.ProductName )
        If @i > 0
              Exec @l = dbo.fn_GetProductNameList5 @CatId, @i
              Set @r =  @l + @r


Select CategoryId, dbo.fn_GetProductNameList5 ( CategoryId, COUNT(ProductName) )

From Products Group By CategoryId ;

This approach uses recursion so before applying it keep in mind that the maximum nest level in T-SQL is 32.

5.      Pivoting Method:

            Select CategoryId,
            MAX( CASE seq When 1 Then ProductName Else ” END ) + ‘, ‘ +
                    MAX( CASE seq When 2 Then ProductName Else ” END ) + ‘, ‘ +
            MAX( CASE seq When 3 Then ProductName Else ” END ) + ‘, ‘ +
                    MAX( CASE seq When 4 Then ProductName Else ” END )
            From ( Select p1.CategoryId, p1.ProductName,
                        ( Select COUNT(*)
                                    From Products p2
                                    Where p2.CategoryId = p1.CategoryId
                                    AND p2.ProductName <= p1.ProductName )
               From Products p1 ) D ( CategoryId, ProductName, seq )
     Group By CategoryId ;

Well I tried this method in my production environment and it fails or shows some abnormal behavior as the size of the table increases. It’s best to use with smaller datasets only.

So here you are with the solutions.
Which one would you use and why?


Read Full Post »