We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

Medium Priority
366 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

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

Author

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!
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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

Author

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

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

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.