Tuesday, December 20, 2011

MSSQL subquery as a list.. i.e. return categories as a select item

If you have ever wondered how to return a list of categories with a select statement heres the answer. It actually comes from StackOverflow.This is a repost so I can find it later as I am sure that I have needed to do this about 20 times in the past.

This is Really clever and full credit goes to this really Smart guy Niikola


Here is a simple-ish query, gets some user data along with the list of categories. The Category table is separate with a linking table called contact_category_xref which holds the category id and the contact id associated.

SELECT
c.id,
c.FirstName,
c.LastName,
c.email,
c.City,
c.State,

Stuff((select ','+cat.name from category cat left join 
        contact_category_xref x
        on cat.id = x.catid
        where x.contactid = c.id
        For XML PATH('')),1,1,'') as categoryList
FROM contacts c 


Ill attempt some explanation, feel free to jump in and offer corrections please.

The first bit of Magic comes in the form of the "For XML Path('')" command in sql returns the query columns as XML which is incredibly handy.

  "pizza,pie"

Now to get rid of the xml portion concatenating the comma before the cat.name returns the list without XML. example ',pizza,pie,cookies,snickers'
I have no Idea how this works or why but it does. Please feel free to explain if you know.

So Whats left is the STUFF command which removes the first character for 1 character in length and replaces it with ''. Which means we are left with our nicely formatted list 'pizza,pie,cookies,snickers'.

Pretty COOL i must say.


Stumble Upon CodePyro

1 comment:

lin liyi said...

I only wish the dial designs were a bit more logical and educational for young minds. The rolex replica store allows for kids just learning how to tell time on analog dials to easily count numerals and easily spot indexes along with easy to spot thick hands rolex replica uk sport a simple and classic looking satin finished rolex replica watches steel case. The strap variety is welcoming, and I can even see a few adults being a bit rolex submariner replica over the design options. All have fun and appealing colors and designs that I think kids will really enjoy. Rolex collection is more playful with a combination of colors and animal faces as the hour markers. The dial looks nice, but I think there is a bit of a missed opportunity. The replica watches are more or less random. Meaning there does seem to be an association with the hour marker the replica watches represents and that hour.

Post a Comment