0

So I have specification tables that relate to products that vary by product type. I need a stored procedure that will return a single-row of unique comma-delimited values in each of the columns (used for site navigation code) in a spec table (or a resultset of said spec table).

I hope this makes sense.

Thank you all for the responses so far. I will explain in more detail...

Each spec table has a different number of columns (and rows, for that matter). Let's say one of them has 50,000 rows and a manufacturer column. Now let's assume there are 5 unique manufacturers in the there as well. Let's also assume the same table also has a color column that shares 7 colors among all 50,000 rows.

My (currently imaginary) stored procedure should return one row with the following columns...

  • Manufacturer:
    Manu1,Manu2,Manu3,Manu4,Manu5
  • Color:
    color1,color2,color3,color4,color5,color6,color7

I intend to use this result to build my site navigation.

m-albert
  • 1,089
  • 8
  • 15

1 Answers1

1

Assuming I've understood:

declare @productTypes table (Id int, Name nvarchar(32))
declare @products table (Id bigint not null identity(1,1), ProductType int, ProductName nvarchar(32))

insert @productTypes (Id, Name)
select 1, 'Food'
union
select 2, 'Drink'

insert @products (ProductType, ProductName)
select 1, 'Chips'
union
select 1, 'Fish'
union
select 2, 'Guinness'
union
select 2, 'Water'
union
select 2, 'Pan Galactic Gargle Blaster'

select pt.Name 
, STUFF
(
    (
        Select ',' + a.ProductName
        from @products a
        where a.ProductType = pt.Id
        FOR XML PATH('')),1,1,''
) ProductsOfType
from @productTypes pt

SQL Fiddle: http://sqlfiddle.com/#!6/e80ff/1

Results:

Name    ProductsOfType
Food    Chips,Fish
Drink   Guinness,Pan Galactic Gargle Blaster,Water
JohnLBevan
  • 22,735
  • 13
  • 96
  • 178
  • Can you give any insight on the performance of this solution? – m-albert Apr 26 '14 at 20:31
  • Also, another thought... What if the code doesn't know exactly what columns its dealing with? Is there an equally-elegant way to handle a situation like that? – m-albert Apr 26 '14 at 20:33
  • 1
    @m-albert : Not sure on performance for large amounts of data; it's better to use a CLR function if you're concerned about that. That said, there's another post, http://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings, which details performant aggregate string concatenation. – JohnLBevan Apr 26 '14 at 20:37
  • 1
    @M-albert if you didn't know the column names you could potentially use dynamic SQL (i.e. build up the SQL in an `nvarchar(max)` variable, then run with `exec(@sql)`; though dynamic SQL's not allowed within stored procedures, so wouldn't be valid for your original question – JohnLBevan Apr 26 '14 at 20:39
  • ps. similar questions/answers via this one, should you be interested: http://stackoverflow.com/questions/5031204/does-t-sql-have-an-aggregate-function-to-concatenate-strings – JohnLBevan Apr 26 '14 at 20:43
  • Dynamic SQL works just fine in stored procs (I am using it now). I think you mean UDF's. :) – m-albert Apr 26 '14 at 20:46