Concatenate Multiple Rows into Single Row using XML PATH

Use Northwind;
GO

select 
  c.CustomerID, c.CompanyName, c.City, c.Country
  , [Orders] = 
  STUFF((
      select ', ' + CAST(o.OrderID as VARCHAR) 
      + ' (' + convert(varchar, o.OrderDate, 101) + ')'
      from Orders o  
      where o.CustomerID = c.CustomerID
      for XML PATH ('')), 1, 2, '')
from Customers c
left join Orders o on o.CustomerID = c.CustomerID
group by 
c.CustomerID, c.CompanyName, c.City, c.Country
order by c.CustomerID

Results: 

CustomerID	CompanyName	Orders
ALFKI	Alfreds Futterkiste	10643 (08/25/1997), 10692 (10/03/1997), 10702 (10/13/1997), 10835 (01/15/1998), 10952 (03/16/1998), 11011 (04/09/1998)
ANATR	Ana Trujillo	10308 (09/18/1996), 10625 (08/08/1997), 10759 (11/28/1997), 10926 (03/04/1998)
ANTON	Antonio Moreno	10365 (11/27/1996), 10507 (04/15/1997), 10535 (05/13/1997), 10573 (06/19/1997), 10677 (09/22/1997), 10682 (09/25/1997), 10856 (01/28/1998)

Keep SSRS staying active

It does take a long time for the first SSRS report to run after worker process shutdown (default = 7200 min or every 12 hours). This behavior is a nuisance but it is due to the many chores that take place to initialize SSRS service.

The following article seems to be a good way to automate to keep SSRS stay active at all time.

 http://gallery.technet.microsoft.com/scriptcenter/SSRS-Wake-Up-Call-b2026b2c#content

Good Pivot Example for T-SQL

Pivot in T-SQL is very useful for ad-hoc reports. It's syntax is easy to forget. I found the following example from here. Very good example indeed.


SELECT pId,_year,amount
FROM Products

..produces the following.

pId _year amount

124 2001 125

125 2001 454

126 2001 75

127 2002 256

128 2004 457

129 2004 585

130 2002 142

131 2002 785

132 2005 452

133 2005 864

134 2005 762

135 2004 425

136 2003 452

137 2003 1024

138 2003 575

..using Pivot as follows..

SELECT * FROM

(

SELECT pId,_year,amount

FROM Products

)t

PIVOT (SUM(amount) FOR _year

IN ([2001],[2003])) AS pvt

.. You have this result... Nice!

pId 2001 2003

124 125 NULL

125 454 NULL

126 75 NULL

127 NULL NULL

128 NULL NULL

129 NULL NULL

130 NULL NULL

131 NULL NULL

132 NULL NULL

133 NULL NULL

134 NULL NULL

135 NULL NULL

136 NULL 452

137 NULL 1024

138 NULL 575

.. Now to make it better by implementing the year as dynamic query..

You can dynamically generate _year list as follows.

DECLARE @years VARCHAR(2000)

SELECT  @years = STUFF(( SELECT DISTINCT

'],[' + ltrim(str(_year))

                        FROM    Products

                        ORDER BY '],[' + ltrim(str(YEAR(_year)))

                        FOR XML PATH('')

), 1, 2, '') + ']'

.. which returns [2001],[2002],[2003],[2004],[2005]

.. To complete the PIVOT query with dynamically generated year list...

DECLARE @query VARCHAR(4000)

DECLARE @years VARCHAR(2000)

SELECT  @years = STUFF(( SELECT DISTINCT

'],[' + ltrim(str(_year))

                        FROM    Products

                        ORDER BY '],[' + ltrim(str(YEAR(_year)))

                        FOR XML PATH('')

), 1, 2, '') + ']'

SET @query =

  'SELECT * FROM

  (

SELECT pId,_year,amount

FROM Products

  )t

  PIVOT (SUM(amount) FOR _year

  IN ('+@years+')) AS pvt'

EXECUTE (@query)

.. And the final result...Nice!!!

pId 2001 2002 2003 2004 2005

124 125 NULL NULL NULL NULL

125 454 NULL NULL NULL NULL

126 75 NULL NULL NULL NULL

127 NULL 256 NULL NULL NULL

128 NULL NULL NULL 457 NULL

129 NULL NULL NULL 585 NULL

130 NULL 142 NULL NULL NULL

131 NULL 785 NULL NULL NULL

132 NULL NULL NULL NULL 452

133 NULL NULL NULL NULL 864

134 NULL NULL NULL NULL 762

135 NULL NULL NULL 425 NULL

136 NULL NULL 452 NULL NULL

137 NULL NULL 1024 NULL NULL

138 NULL NULL 575 NULL NULL

Check if Enter Key has been pressed

I keep forgetting the simple way to check if the Enter Key has been pressed while filling out a form. All too often, users assume the presence of default button for the Enter Key next to textboxes that they fill out...

/*
1. Check if Enter Key has been pressed
2. If Enter Key has been pressed, 
   bind click event with a handler to the default button among siblings.
3. Trigger click() event
*/
$('input[type=text]').bind({   
  keypress: function( event ) {
    if( checkIfEnterKeyPressed() ){ //1.Check if Enter Key has been pressed
      var message = 
        'Enter key has been pressed while in ' + $(this).attr('id') + '.';
      $(this).siblings('.defaultButton').bind( //2. Bind click event handler
        'click', 
        { msg: message }, 
        function( event ){
          alert( event.data.msg + '\n' + $(this).val() + ' has been clicked()' );
        }
      ).click(); //3. Trigger click event
    }
  }
});

function checkIfEnterKeyPressed() {  
  var keycode = ( event.keyCode ? event.keyCode : event.which ); 
  return ( keycode == '13' ? true : false );
}