Combine Multiple values into one row

[BeachSandGuy]

Hi, I have been searching and have been unable to find a solution to the following.
I have multiple tables, here are some examples.
t1
Policy Name EffDate
1 Walmart 1/1/09
2 Target 1/1/09

t2
Policy Phone Type
1 8005555551 1
1 8005555552 2
2 8005555553 1
2 8005555554 2
2 8005555555 3

t3
Type Description
1 Products
2 Restaurant
3 Vision

I can join the tables together to get the following:
Select t1.Policy, t1.Name, t2.Phone, t3.Description
From t1
Left outer join t2 on t1.Policy = t2.Policy
Left outer join t3 on t2.Type = t3.Type

Results would be:
1 | Walmart | 8008555551 | Products
1 | Walmart | 8008555552 | Restaurant
2 | Target | 8008555553 | Products
2 | Target | 8008555554 | Restaurant
2 | Target | 8008555555 | Vision

How could I do this so that the results would be the following with one client per line?

1 | Walmart | 8008555551 | Products | 8008555552 | Restaurant
2 | Target | 8008555553 | Products | 8008555554 | Restaurant | 8008555555 | Vision

Just a note I have multiple tables that I'd combine together, so for example, might be another for manually entered volumes per department. None of this is aggregated though, so just straight data, no need to sum or perform calculation on any of it.
Thanks in advance for any assistance.

Ian

 

[gmmastros]

Do you want this data returned as a delimited string, or do you want separate columns for each type/description?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BeachSandGuy]

Separate columns.

Thanks.

Ian

 

[gmmastros]

The problem with separate columns is that you may not know how many columns to return. I mean.... your lookup table has Products, Restaurant, and Vision. What if you wanted to add a couple columns to this table so you can start storing "Customer Service", "Lawn & Garden", etc....

What I'm trying to say is.... this is possible as long as the list of lookup type's does not change. The problem with a static lookup table is that it's not at all flexible (to accommodate changing application requirements). I can pretty much guarantee that no matter how exhaustive you build your list, there will always be something new coming along to break your code, requiring a code change.

My best suggestion for you would be to perform this functionality within your front end application.

The example below shows some table variables with hard-coded data to show you how this can be accomplished.

Declare @t1 Table(Policy Int, Name VarChar(100), EffDate DateTime)
Insert Into @T1 Values(1,'Walmart','1/1/09')
Insert Into @T1 Values(2,'Target ','1/1/09')

Declare @t2 Table(Policy Int, Phone VarChar(10), Type Int)
Insert Into @t2 Values(1,'8005555551', 1)
Insert Into @t2 Values(1,'8005555552', 2)
Insert Into @t2 Values(2,'8005555553', 1)
Insert Into @t2 Values(2,'8005555554', 2)
Insert Into @t2 Values(2,'8005555555', 3)

Declare @t3 Table(Type Int, Description VarChar(100))
Insert Into @T3 Values(1,'Products')
Insert Into @T3 Values(2,'Restaurant')
Insert Into @T3 Values(3,'Vision')

Select t1.Policy, 
       t1.Name, 
       Min(Case When T3.Description = 'Products' Then t2.Phone End) As Products, 
       Min(Case When t3.Description = 'Products' Then T3.Description End) As ProductsNumber,

       Min(Case When T3.Description = 'Restaurant' Then t2.Phone End) As Restaurant, 
       Min(Case When t3.Description = 'Restaurant' Then T3.Description End) As RestaurantNumber,

       Min(Case When T3.Description = 'Vision' Then t2.Phone End) As Vision, 
       Min(Case When t3.Description = 'Vision' Then T3.Description End) As VisionNumber
From   @t1 T1
       Left outer join @t2 T2 on t1.Policy = t2.Policy
       Inner join @t3 T3 on t2.Type = t3.Type
Group By T1.Policy, T1.Name

I encourage you not to use this code though, because it is very limiting in it's flexibility.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BeachSandGuy]

Hey, thank you George! If I wanted to concatenate instead, would that be more flexible as far as varying fields? How would that be done?

Ian

 

[gmmastros]

Concatenation would be a lot more flexible. Take a look here:
thread183-1159740



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom