• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

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

0
mobrien118
Asked:
mobrien118
  • 4
  • 2
2 Solutions
 
BrandonGalderisiCommented:
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

0
 
mobrien118Author Commented:
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!
0
 
BrandonGalderisiCommented:
Try:

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

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mobrien118Author Commented:
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

0
 
mobrien118Author Commented:
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

0
 
mobrien118Author Commented:
Better formatted:
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

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now