concatenating calculations from different table columns into a string

Question

I asked a similiar question for Concatenating rows from an alias computed column and got the answer I needed then. This question is a little different in that one of the columns is from a different table. Here are the tables and Values:
Dish table

Name          CookTimeMins Yield ServingsLeft
Stroganoff    150          10    3
Lasagna       180          24    3
Chicken Carbonara 175      13    0
Chicken Fettucine 15       3     6
Chili Cheeseburger 10      2     1
Chicken Fettucine  10      5     0

where Yield and ServingsLeft Columns are integer columns and CookTimeMin column is a float column.

Dish2 table

Name          TestIntCol 
Stroganoff    12
Lasagna       12
Chicken Carbonara 12
Chicken Fettucine 12
Chili Cheeseburger 12 
Chicken Fettucine  12

What I want to achieve is this:

Name                 Laser
Chicken Carbonara    0.00
Chicken Fettucine    2.00,0.00
Chili Cheeseburger   12.00
Lasagna              4.00
Stroganoff           4.00

However what I'm getting is this:

Name                 Laser
Chicken Carbonara    0.00,2.00,4.00,12.00
Chicken Fettucine    0.00,2.00,4.00,12.00
Chili Cheeseburger   0.00,2.00,4.00,12.00
Lasagna              0.00,2.00,4.00,12.00
Stroganoff           0.00,2.00,4.00,12.00

My code is here:

select I.Name,
--if we hit a divide by zero error, set to null and then set the null value to zero.
(substring((select ', '+cast(ISNULL(cast(L.TestIntCol/NULLIF(K.ServingsLeft,0)as decimal(10,2)),0)as   varchar(max)) from Dish2 L
    join Dish K on L.Name = K.Name
    --group on the calculation of the expression
    where L.Name = K.Name group by ISNULL(cast(L.TestIntCol/NULLIF(K.ServingsLeft,0)as decimal(10,2)),0) FOR XML PATH('')), 2, 1000)) as Laser
from Dish I
join Dish2 J on I.Name = J.Name
group by I.Name

I have read that grouping by the expression result should help but it hasn't. I have been stuck on this for a couple days. I have looked around but haven't found a right answer for my scenario. Thanks for the help.

thanks for the code fix. I couldn't find how to format it correctly. — gamers542, Jan 22 '15 at 18:24
It is an aliased column that contains the result of the calculated columns. I use it as a default name when I can't think of a column name. — gamers542, Jan 22 '15 at 18:51

score 1 · Accepted Answer · answered Jan 22 '15 at 19:29

1

You can use a CTE to make things simpler:

;WITH CTE AS (
    SELECT DISTINCT I.Name, 
           ISNULL(CAST(J.TestIntCol / NULLIF(I.ServingsLeft,0) AS DECIMAL(10,2)), 0) AS tempLaser
    FROM Dish I
    INNER JOIN Dish2 J ON I.Name = J.Name
)
SELECT 
  C.Name, STUFF((SELECT ', ' + + CAST([tempLaser] AS VARCHAR(MAX)) 
                 FROM CTE 
                 WHERE (Name = C.Name) 
                 FOR XML PATH('')), 1, 2, '') AS Laser 
FROM CTE C
GROUP BY C.Name

In order to produce the required result set, provided in the OP, I had to use DISTINCT inside the CTE since (Chicken Fettucine 12) is repeated twice in the sample data provided in the OP.

answered Jan 22 '15 at 19:29

Giorgos Betsos

71,379
9
63
98

This worked as I wanted. Will mark as correct. Question: Are CTEs better to use than subqueries? In my real-life application I use subqueries to achieve things that I asked in the question. – gamers542 Jan 22 '15 at 19:52
@gamers542 I personally prefer them over subqueries, in cases like this, where a `CTE` is used more than once. I think that performance-wise they are equivalent though. You may have a look here http://stackoverflow.com/questions/11169550/is-there-a-speed-difference-between-cte-sub-query-and-temporary-table – Giorgos Betsos Jan 22 '15 at 20:46

concatenating calculations from different table columns into a string

1 Answers1