asked on

SQL: Create "roll-up" of rows and join to another table based on key?

Hi,

Assuming a structure similar to this:

Table1 (projects):
ProjectNum [key]
...

Table2 (project tasks):
TaskNum [key]
ProjectNum
ColorCode
...

where tasks all belong to a project, I'd like to run a query such that the task "ColorCodes" all show up in a single field in the result.

For instance, given the following data:

Table1:
ProjectNum
--------------
12345
54321

Table2:
TaskNum ProjectNum ColorCode
----------- --------------- -------------
1 12345 Red
2 12345 Blue
3 12345 Blue
4 54321 Blue
5 54321 Green

I could return the following data (with the colors in alpha. order, if possible):

Result:
ProjectNum Colors
-------------- -------
12345 Blue, Red
54321 Blue, Green

I have the following 2 queries that sort of get where I am going on the roll-up thing, but am having trouble figuring out how to join them.

The attached Query code Seems to "roll up" ALL of the colors (not by project yet!!!) I have another query where I declare a function and include that in my main select, but I can't get it to run this query based on the ProjectNum! If I could, I think that would solve the problem.

Any help would be AWESOME!

Thanks!!!!!!!

SELECT DISTINCT STUFF( (SELECT DISTINCT ', ' + [ColorCode] from [Table2] ORDER BY ', ' + [ColorCode] FOR XML PATH('') ),1,2,'') as [Colors] FROM [Table1]

Open in new window

BrandonGalderisi

You need a scalar valued function to return the concatenated list

create function dbo.fn_GetColorCodes(@ProjectNum int)
returns varchar(1000)
as
begin
declare @ret varchar(1000)
select @ret=isnull(@Ret+', ','')+ColorCode
From Table2
where ProjectNum = @projectNum

end
go

Use it like this:

select ProjectNum, dbo.fn_GetColorCodes(ProjectNum)
from table1

Open in new window

mobrien118

ASKER

Thanks BrandonGalderisi, but this keeps returning NULL for the "roll-up" fields, for some reason! (I've already tried that solution before)

Also, I don't think that will only select the DISTINCT colors (I didn't state it outright, but in my example, I listed Blue twice and only showed it once). Also, I don't think that would display the colors in alphabetical order, which i also need so that I can later use color combinations as parameters.

That's close, it's just the function that needs changing.

Can you help me get past this hump?

Thanks again!

SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mobrien118

ASKER

OK, now it is returning ONE ColorCode for each one. There should multiples (can confirm it).

I have this (the attached) query working ALMOST (and I like it a lot better because I don't like managing a function).

The problem is that it is returning all of the color codes in each result, I just don't know how to make to get the ones only for the current Project! However, if I specify "WHERE ProjectNum=12345" for one of them in the STUFF function query, it returns all of the correct ones for that project, but it is in all of the results. I need a way to link them up... Maybe my way won't work, but I like it better, if it can. Like I said, I hate using UDFs

SELECT [ProjectNum],
  STUFF(
    ( SELECT DISTINCT ', ' + [ColorCode] from [Table2]
      ORDER BY ', ' + [ColorCode]
      FOR XML PATH('') ),1,1,''
    )
  AS [Colors]
  FROM [Table1]

Open in new window

mobrien118

ASKER

NM, got it working!

I'm surprised this even worked!!!

I'll still give you credit since, even though I didn't use your solution, it was pretty close (and I still don't understand why it didn't work).

Cheers!!!

SELECT [ProjectNum]
      STUFF( (SELECT DISTINCT ', ' + [ColorCode] from [Table2] WHERE ProjectNum=[Table1].[ProjectNum] ORDER BY ', ' + [ColorCode] FOR XML PATH('') ),1,2,'') as [Colors]
  FROM [Table1]

Open in new window

ASKER CERTIFIED SOLUTION

mobrien118

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SQL: Create &quot;roll-up&quot; of rows and join to another table based on key?

SQL: Create "roll-up" of rows and join to another table based on key?