2/9/15

T-Sql concatenate rows using STUFF and For XML PATH

I am using the FOR XML PATH to concatenate rows together in a comma separated fomat. The STUFF function is used to remove the first comma of the result string. This is for scenario where you are looking to group the rows into single row based on certain condition. In the demo example, we're simply displaying the user names together for the particular city.

-- Create temporary table for users (id, first_name, city)
-- id is auto increment primary key for our users table.
DECLARE @users Table(id int NOT NULL IDENTITY(1,1) PRIMARY KEY, first_name varchar(50), city varchar(50));

-- Insert some demo records so that we can concanitate user first names by city
INSERT @users(first_name, city)
Values
('John', 'New York City'),
('Mark', 'San Francisco City'),
('Bob', 'New York City'),
('Kelly', 'New York City'),
('Lisa', 'San Francisco City'),
('Christian', 'Chicago'),
('Angel', 'New York City');

-- Our table records
select * 
from @users;


-- Query to concatenate names of users for the city (comma separated display)
-- STUFF Syntax: STUFF(character_expression, start, length, replacewith_expression)
-- You can check out: https://msdn.microsoft.com/en-us/library/ms188043.aspx
-- For XML Path Mode
-- You can check out: https://msdn.microsoft.com/en-us/library/ms189885.aspx
SELECT distinct(tu1.city) as [City],
 STUFF((
   select ',' + tu2.first_name as [text()]   
   FROM @users as tu2
   where tu2.city = tu1.city
   FOR XML PATH('')
   ), 1, 1, '') as [Users from the city]
from @users as tu1;


-- Plain xml using for xml path command
-- using @ command makes 'id' to be used in the 'attributes' for the row element
-- other columns are displayed as xml elements within row
-- You can check out here: https://msdn.microsoft.com/en-us/library/ms189885.aspx
SELECT u.id as '@id', u.first_name as 'name', u.city as 'city'
FROM @users as u
FOR XML PATH;


  John
  New York City


  Mark
  San Francisco City


  Bob
  New York City


  Kelly
  New York City


  Lisa
  San Francisco City


  Christian
  Chicago


  Angel
  New York City

3/5/14

CSS tweak to vertical orientation of text in table header

I was looking to do something regarding the table I use for the displaying the data set. The trouble was making the table headers
orient to be the veritical or rotate (-90degrees). It seems that if you try to directly apply the transform css property on the
itself, it is difficult to control its boundry and growth dynamically. The problem seems to be solved by using two divs inside the table header instead.
itself.


Sample CSS & html code to make it happen:-

JSFiddle: http://jsfiddle.net/dhunju/c9tH4/3/

th, td {
    font-family:'Segoe UI', Tahoma, Helvetica, sans-serif;
    border: 1px solid;
    text-align:center;
    padding: 0.5em;
}
   


Country Name
Capital City
Phone Code & Postal Code
Nepal Kathamandu 977-1 44600
United States Washington D.C. 202 20001

3/10/12

Dropbox

It is almost hard to believe that anyone having not heard of Dropbox. For those who haven't heard about dropbox, it is file sync application. It allows you to sync your files through multiple computers be it running a windows operating system or a Apple's OS X or other Linux distribution.

Also, you can use dropbox in your mobile phones. Phones that run with the android OS or iOS have dropbox application which can be used to get files to your phones.

One of best part of using dropbox is its easy to use user interface. Sometimes you know when you use certain applications or products that it is just good and easy to use. You feel it I can say. Take Facebook for example, it is just easy and pleasing to your eyes. Dropbox is one of those application. And I have been using dropbox since its early days and now been couple of years. With dropbox ever growing community it seems to be integrated into our lives.

Dropbox is like a cloud based backup for you. In note of backup, make sure you back up your files in some way if not dropbox, I have had to deal with people who had crashed their hard drive. Usually, you can recover files if they've not been overridden using some recovery software that are available. But still in certain cases when the hard drive becomes unreadable then there is no one to blame than the user himself. Hard drive is a mechanical thing and as all mechanical things they have a time span and it is bound to break down. Way of life my friends. 

Dropbox lets basic users with 2GB of free space. If you are planning to sync your documents folder with it then it is a good amount of space. However, if you are thinking in terms of movies than it is a bad one. One movie tends to be like a GB or more. Another good thing is that it lets user publish folders as public. It means that you can share those folders with your friends whom you choose to allow. Or if you are starter web developer, put in your "index.html" file over here. Ya, its that hello world page which we get to do your first class. Anyways, nothing special.

If you don't have dropbox installed yet, I would recommend going ahead and starting with the free version. Tip: Invite your friends to download dropbox and you would get extra space if your friends sign up. I used that when I first used dropbox to ramp up some space in my account. However, the pro edition is cheap and convenient.

 Get Dropbox, Dropbox Website Link

3/9/12

Famous Quotes Bank - Android Application

Famous Quotes Bank Application:

My first android application and getting started with the mobile development. It provides functionality to search for quotes through the search bar and also provide with predefined search options based on authors and category.

I make a REST call to get the quotes to the device. Hence, in slow network connectivity, the application seem to have a slight lag of couple of seconds. But overall, it is an easy awesome way to get quotes to your phone. The best part the collection is on the cloud, the user would have new quotes and can search for quotes on any author or topic using keyword.

Another best part of the application is the ability to share quotes on facebook and other social media straight from the application. So, you can basically click the quote and it would be posted on your wall so as your friends can see. Sharing is always great and I have included the facebook API into the application.

Working on:

Have not implemented the method to allow user to save quotes to their phone. I plan to add the feature in my next version. Its always handy to be able save quotes for later references.

Market Link:


 
Use the QR Code to download the application:




Again, you can't connect the dots looking forward; you can only connect

them looking backwards. So you have to trust that the dots will somehow connect

in your future. You have to trust in something - your gut, destiny, life, karma,

whatever. This approach has never let me down, and it has made all the

difference in my life.


- Steve Jobs