RSS 2.0 Feed
RSS 2.0


Atom 1.0 Feed
Atom 1.0

  Flattening Out Data with One of the Coolest SQL Tricks Ever 


Not sure why, but I seem to be on a T-SQL kick lately - so here's another T-SQL post. One of my favorite T-SQL hacks ever is one that can flatten out data by taking a value from multiple rows and concatenating the values into a single string.

Let's say, for example, that you have a “training class” table with a 1:M child table containing dates the class will be held and you want to just have a single string that lists the dates for each class. Or maybe that you have a “project” table with a 1:M child, each row containing a person assigned to that project and you want to have a list of all the members of the project like this: “Ryan Farley, Bobo Brown, Joe Blow”. Apparently this trick has been around a while. I first learned of it from Scot Becker a year or so ago and I've loved it ever since.

Take a look at this example of parent-child tables and their data:

Now, if I wanted to get a single string of child records for a given parent I could do this:

declare @list varchar(1000)
set @list = ''

select @list = @list + fieldvalue + ' ' from childtable where parentid = @id

return @list

This would result in the following value returned for the ParentID of 1:

Value 1-1 Value 1-2 Value 1-3

This can have some cool uses. Let's say you want to kick out something to a customer outlining the products in their order. Just the names delimited by commas. You could use this (using the Northwind database)

declare @prodlist varchar(1000) 
set @prodlist = ''

select 
    @prodlist = @prodlist + p.productname + ', ' 

from
    orders o 

    inner join [order details] o2 
    on o.orderid = o2.orderid 

    inner join products p 
    on o2.productid = p.productid 

where 
    o.orderid = @id

--trim off extra comma at end
if @prodlist <> '' set @prodlist = left(@prodlist, len(@prodlist) - 2)

return @prodlist

Now you'll have a comma delimited string of products for the order to use as you need. No cursors/loops used. I've used this in many different ways, from listing a string of dates for an event, or even building a Page1/Page2/Page3.aspx kind of URL for hierarchical pages in a CMS/portal system. Quite a cool trick IMO.

A few things to keep in mind. This is in no way an optimal route if you have a lot of rows of data to work with. This will be slow with medium to large sets of data. You can only use a single field in this since it is an assigning select statement and cannot be mixed with data-retrieval, so the usual route is to stick it in a function where you pass in a ID and get back the string. Add the call to the function in your data-retrieving select statement and things will get slow if you have the potential for a large amount of rows. But with that said, Scot's post outlines a few crude speed tests and states the following:

To do an unscientific performance test, I created a table loaded with a million numbers and summed them via a) the TSQL sum aggregate function, b) the method above and c) a cursor. On my (old) laptop, the sum function took 1,350 milliseconds, the method above took 1,213 milliseconds, and the cursor took 67,206 milliseconds.

If the trick fits the need, I use it. This one is the cat's meow. I fell in love with this one the moment I read it from Scot a year+ ago. You had me at select @s = @s + person_name ;-)




                   



Leave a comment below.

Comments

  1. Bryant Likes 2/18/2005 9:16 AM
    Gravatar
    And you can shorten it a little more by using coalesce:

    declare @prodlist varchar(1000)

    select
    @prodlist = coalesce(@prodlist + ', ' + p.productname, p.productname)
    from
    orders o

    inner join [order details] o2
    on o.orderid = o2.orderid

    inner join products p
    on o2.productid = p.productid

    where
    o.orderid = @id

    return @prodlist
  2. Ryan Farley 2/18/2005 9:54 AM
    Gravatar
    Bryant,

    Awesome tip! Thanks.

    -Ryan
  3. Ryan Farley 3/7/2006 11:53 PM
    Gravatar
    Hi, a cunning trick :-)

    I use this trick in one of my sql scripts. But of some mystic reason the database won’t store more than 266 characters. Maybe there is database obstruction of some kind, but have you any clue to solve this problem.

    Regards
    Geir

    3/8/2006 9:53 AM
  4. fizzingwizbee 5/4/2006 12:05 PM
    Gravatar
    Anybody ever used this in a subquerey before, seems like it would work as it only returns one result, it would be very useful to know as it seems it has a lot of usefullness for transforming data. . . .
  5. Joey 8/13/2006 9:21 PM
    Gravatar
    This tip was very useful to me. Thanks! However, do you know if it might be version-dependent? I used this query to perform a similar operation to the one you described, with success. More recently on a new machine (also running SQL 2000, but possibly a different minor version), it returns only a single list item:

    DECLARE @t varchar(1000)
    SELECT TOP 10 @t = ISNULL(@t + ',', '') + @t
    FROM Table1
    WHERE ...
    GROUP BY Table1.field1
    ORDER BY COUNT(*) DESC -- this is the problematic line
    PRINT @t

    If I remove the ORDER BY clause, it returns all of the list items. It's important that these be returned in this order, though. Any ideas?
  6. Mathew Payne 8/30/2006 2:09 AM
    Gravatar
    I have create a SP but keep getting this error:

    Msg 245, Level 16, State 1, Procedure get_opids, Line 14
    Conversion failed when converting the varchar value 'L' to data type int.

    here is the SP code :

    declare @list varchar
    set @list = ''
    select TOP 10 @list = @list + opid + ',' from fn_Get_Alert_Casedetails(@GlobalID) where Globalid = @GlobalID
    return @list

    i dont understand why this stupid thing is trying to convert to a int

    could someone please help me ?
  7. Ryan Farley 8/30/2006 7:14 AM
    Gravatar
    Mathew,

    It is likely implicitly coercing the values in opid as ints because of the values in the return. Try casting the field to a varchar (either in the query you listed, or in the UDF).

    ie:
    select TOP 10 @list = @list + cast(opid as varchar(10)) + ','.......

    -Ryan
  8. Tony 11/2/2006 12:49 PM
    Gravatar
    I went a step further and used this method and put a CASE on the resulting string to break it out to individual columns. I used the CASE on the result rather than on the base result set because it allowed me to then use CHARINDEX, SUBSTRING and LEN to break the initial values to columns by searching out commas.

    There were unlimited number of unknown string values in the result set so using a CASE on the base table was not viable to pivot the table.

    I suppose I could use a cursor to pivot the values to columns. If anyone can understand my post, which way do you think would be faster?
  9. William Etheridge 12/14/2006 11:13 AM
    Gravatar
    This is so cool.

    I wish that AND fieldname IN (@list) would work, that would really be useful.

    To the person who got 256 char limit, if you are in Query Anal, you have to set the limit on returned fields up under options.

    William
  10. Bob Clegg 4/18/2007 8:05 PM
    Gravatar
    Hi Ryan,
    Thanks for the tip, you wouldn't have an example where it is used inside another select statement would you? ie as a subquery.
    I am trying to flatten out a phonelist and add the result string to an existing query in a sproc.
    I have built the flattening functionality into another sproc as per your tip, but can't see how to get the 'exec mysproc mycustid' into the larger original select statement.

  11. Rick Smith 1/8/2008 11:18 AM
    Gravatar
    Ryan - you always seem to have the answer I need! Thanks for this, just used it in a UDF inside SalesLogix and it worked as advertised.
  12. Bob 3/19/2008 10:06 AM
    Gravatar
    How would you do this outside of a SP?
  13. Reklam Ajans 8/1/2008 3:18 AM
    Gravatar
    thanks I user the CASE on the results rather than on the base results set because
  14. Ian Brand 8/30/2008 5:37 AM
    Gravatar
    Just brilliant. Many thanks.
  15. Diane 4/16/2010 1:27 PM
    Gravatar
    This works near perfectly for me; however, I can't figure out to apply "distinct" to it. I'm trying to string together all of the property codes each owner owns but some owners own more than one unit at a given property so I get dups in my list. Can you help me to ensure I only get one occurrence for any given property code in my final result? Thanks much!
  16. Diane 4/16/2010 1:34 PM
    Gravatar
    Sorry, I posted too quickly. I used Joey's GROUP BY strategy and solved my problem. Thanks for the VERY COOL tip!!! I didn't want to use a cursor!
  17. Nandu Sasikumar 4/21/2010 2:46 PM
    Gravatar
    Why you need UDF if you have 2005.

    Try this. THis should get that with one sql.

    Select Opportunityid , stuff(
    ( select ','+ A3.FirstName + ' ' + A3.LastName from Opportunity_Contact A2 left join Contact A3
    on A2.Contactid = A3.contactid where A1.opportunityid = A2.Opportunityid for xml path('') )
    ,1,1,'')
    from opportunity A1 where opportunityid = 'O6UJ9A00002E'
    Group By opportunityid
  18. johnny sql 6/25/2010 3:02 PM
    Gravatar
    here is a SQL 2008 solution that is really simple and small in size

    SELECT STUFF(
    (SELECT ',' + columnname FROM tablename WHERE 1=1 ORDER BY 1
    FOR XML PATH(''))
    ,1,1,'') AS value
  19. Jams 7/25/2010 5:29 PM
    Gravatar
    how to apply this trick to produce following result?
    -------------------------------
    1 | Value 1-1 Value 1-2 Value 1-3
    -------------------------------
    2 | Value 2-1 Value 2-2
  20. Jules 8/6/2010 3:03 PM
    Gravatar
    Thanks johnny sql! This allowed me to update a table with flatten data!
  21. 6/27/2011 2:42 PM
    Gravatar
    FLAT &amp;laquo; Developing Matt
  22. 12/3/2011 8:30 PM
    Gravatar
    Subquery spss | Seeyou2004
Comments have been closed on this topic.



 

News


Also see my CRM Developer blog

Connect:              


Sponsor

Sections