combining multiple rows into one row

If there is a table called employee

EmpID           EmpName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I need in this format:

EmpID           EmpName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

1. FOR XML PATH trick and article
2. CLR User defined aggregate
3. for sql server prior version 2005 - temporary tables

An example of #1

DECLARE @t TABLE (EmpId INT, EmpName VARCHAR(100))
INSERT @t VALUES
(1, 'Mary'),(1, 'John'),(1, 'Sam'),(2, 'Alaina'),(2, 'Edward')
SELECT distinct
    EmpId,
    (
        SELECT EmpName+','
        FROM @t t2
        WHERE t2.EmpId = t1.EmpId
        FOR XML PATH('')
    ) Concatenated
FROM @t t1

And that's the results;

You can also try one of the XML methods e.g.

SELECT DISTINCT Issue, Customfield, StringValues
FROM Jira.customfieldvalue v1
CROSS APPLY ( SELECT StringValues + ',' 
              FROM jira.customfieldvalue v2
              WHERE v2.Customfield = v1.Customfield 
                  AND v2.Issue = v1.issue 
              ORDER BY ID 
                  FOR XML PATH('') )  D ( StringValues )
WHERE customfield = 12534 

AND ISSUE = 19602

source : http://stackoverflow.com/questions/8005846/sql-server-combining-multiple-rows-into-one-row

jQuery UI Datepicker and z-Index

The jQuery date picker is a nice little date picking solution that’s easy to use and work with. I’ve used this control in many places and some time ago wrapped it into anASP.NET server control to make it easier to work with yet.

The control works great, but there’s one thing that a number of people using the control have run into:  When the date picker is used with other controls that use z-Indexes extensively it’s quite easy to end up with a date picker that doesn’t pop up properly:

Ooops… not quite the expected behavior, huh?

What’s happening in this example is that the date picker is hosted inside of a position absolute element (the dialog) which sits on top of a modal overlay. Both the modal overlay and the dialog have higher z-Index values than the date picker popup.

There’s a relatively easy fix for this which should work on a page or even application level in most cases:

<style type="text/css">
    #ui-datepicker-div
    {
        z-index: 9999999;
    }
</style>

The pop up window that the date picker uses is called ui-datepicker-div and so you can style the thing with a sufficiently high z-index value that it always pops up above anything else you might have configured.

Fixed z-Index will cause Problems

That solves the problem if your z-index values on pages are relatively fixed. But this may not always be the case. For example, if you have multiple dialogs on a page that are draggable you probably want to have the ability to have dialogs dropped to automatically pop to the top of the zOrder.

zOrder tracking can be a real bitch if you have multiple components or applications all trying to figure out the best way to pop to the top of the stack by utilizing some fixed z-Index value. Just consider the modal popup, the dialog and the date picker. Add to that a few draggable dialogs and the whole concept and you quickly run out of fixed values that work in multiple situations.

When it really comes down to it z-index needs to be managed slightly differently with an easy way to change the z-Index to the maximum value to guarantee that the item ends up on top – generically regardless of what else is on the page.

I talked about this via a jQuery maxZIndex Plug-in a while ago. The plug-in offers an easy way to find the max z-Index used in a page and also assign a new max z-Index to an element. I use this extensively in my draggable component as well as in the modal dialog and other components. Because it’s so short here it is again inline:

$.maxZIndex = $.fn.maxZIndex = function(opt) {
    /// <summary>
    /// Returns the max zOrder in the document (no parameter)
    /// Sets max zOrder by passing a non-zero number
    /// which gets added to the highest zOrder.
    /// </summary>    
    /// <param name="opt" type="object">
    /// inc: increment value, 
    /// group: selector for zIndex elements to find max for
    /// </param>
    /// <returns type="jQuery" />
    var def = { inc: 10, group: "*" };
    $.extend(def, opt);
    var zmax = 0;
    $(def.group).each(function() {
        var cur = parseInt($(this).css('z-index'));
        zmax = cur > zmax ? cur : zmax;
    });
    if (!this.jquery)
        return zmax;

    return this.each(function() {
        zmax += def.inc;
        $(this).css("z-index", zmax);
    });
}

So… this can also be applied to the date picker. Ideally something like this should be used internally by the date picker, but well, we don’t live in an ideal world. The next best thing is that we can hook the datepicker in initialization and use the max zindex plug in to assign the high zindex:

jQuery('#txtDate').datepicker({ showButtonPanel: true, 
  showOn: 'button', 
  buttonImageOnly: true, 
  buttonImage: '/wconnect/images/calendar.gif', 
  beforeShow: function() {$('#ui-datepicker-div').maxZIndex(); },  dateFormat: 'mm/dd/yy' }).attachDatepickerInputKeys();

Now no matter what z-index is used the date pop up always pops up on top of the content:

As I mentioned previously because there are a handful of common things I do to my date picker control I’ve wrapped this thing up into an ASP.NET server control that handles all of these configuration options. You can find this control as part of the West Wind Web Toolkit in the Westwind.Web project if you’re curious.

I suppose the same thing could also be done in JavaScript with some sort of factory function that sets all your default options on the date control. Certainly you don’t want to set those 6 or 7 common settings on each instance.

source : http://www.west-wind.com/weblog/posts/2009/Sep/12/jQuery-UI-Datepicker-and-zIndex