Developing Matt

My Technical Journal

FLAT

leave a comment »

There are lots of methods to flatten a table.  You can cursor at it (loop or use a cte, temp table and build your string), but t-sql is a language of sets and anytime you move away from groups or sets you fitting a round peg in a square hole.  Performance will hurt.

Specifically I wanted to comma separate out a table from something like this

    orderid      name

    1                bob

    1               mary

    1               genie

    2               billy

    2               frank

    2               grace

to something like this

   orderid      name

     1              bob, mary, genie

     2              billy, frank grace

If you are in 2005 or 2008 try the for xml command.  A good example can be found by blaksheep here

If you are in 2008 try on a little RANK. 

If you are in 2000

you can use a function

see http://ryanfarley.com/blog/archive/2005/02/17/1712.aspx

Advertisements

Written by matt

June 26, 2011 at 3:42 pm

Posted in T-Sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: