Combining data into a single cell while summing a seperate cell

Question

I don't know how to word my question very well, so I will start with the data being returned:

prop_id | assessed_value | sale_id

35004 | 401200 | 1920831

35005 | 40500 | 1920831

35023 | 11300 | 1920831

34380 | 139100 | 1915846

127959 | 286400 | 1915882

I would like it to return:

prop_id | assessed_value | sale_id

35004, 35005, 35023 | 453000(Sum of the 3 parcels) | 1920831

34380 | 139100 | 1915846

127959 | 286400 | 1915882

So my main goal is to combine all the parcels on the sale_id field into a string, but only when a sale id has multiple prop_id, and then sum up the assessed value of all those. This is the query I am using to get the first set of data...

select 
pv1.[prop_id],
pv1.[assessed_val],
ld1.[sale_id]
from dbo.land_detail as ld1     
join dbo.property_val as pv1 on 
pv1.[prop_id] = ld1.[prop_id] and
pv1.[prop_val_yr] = ld1.[prop_val_yr] and
pv1.[sup_num] = ld1.[sup_num]
left join dbo.sale as sale1 on  
sale1.[chg_of_owner_id] = ld1.[sale_id]
where   
pv1.[prop_inactive_dt] is null
order by sale_id, prop_id

prop_id is of data type INT
assessed_val is of data type numeric(14,0)
sale_id is of data type INT

Please post your "before" data in a consumable format. A picture of data isn't data. — Jason A. Long, Aug 03 '17 at 18:57
Posting your raw data structure, along with what you would expect your result set to look like, would be very helpful. — Eli, Aug 03 '17 at 18:59
this question has been asked so many times. Google "How to use STUFF" Function in mssql — Kashif Qureshi, Aug 03 '17 at 19:02
Slight detour...if you care about the accuracy of your results you should stop littering your queries with that NOLOCK hint. It can and will return missing and/or duplicate rows, not to mention a whole list of other pretty spectacular things. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ — Sean Lange, Aug 03 '17 at 19:33
Your data shows that you already have a Ratio value and your query still is computing it. Please provide your table structures, sample data and result. — Ferdinand Gaspar, Aug 03 '17 at 19:55

score 0 · Answer 1 · answered Aug 03 '17 at 20:01

Put everything into a temp table (#temp) as below. Then make a self join to select the values. There are several ways of doing concatenation using (SUFF(), XML PATH, etc). Search on the internet on how to concatenate rows into comma separated string refer to this link for example

select 
pv1.[prop_id],
pv1.[assessed_val],
ld1.[sale_id],
sale1.[sl_price],
sale1.[sl_dt],
(pv1.[assessed_val]/sale1.[sl_price]) as Ratio
--------Temp Table------------
INTO #temp
------------------------------
from dbo.land_detail as ld1 with(nolock)    
join dbo.property_val as pv1 with(nolock) on    
pv1.[prop_id] = ld1.[prop_id] and
pv1.[prop_val_yr] = ld1.[prop_val_yr] and
pv1.[sup_num] = ld1.[sup_num]
left join dbo.sale as sale1 with(nolock) on 
sale1.[chg_of_owner_id] = ld1.[sale_id]
where   
sale1.sl_dt <= '04/30/16' and
sale1.sl_dt >= '05/01/15' and
pv1.[sub_type] = 'r' and
pv1.[prop_val_yr] = 2016 and
pv1.[prop_inactive_dt] is null
order by sale_id, prop_id

Ferdinand Gaspar · Accepted Answer · 2017-08-03T22:54:08.457

0

You can use WITH to create a temporary table then you can use STUFF() FOR XML PATH to concatenate the prop_id field into one row

WITH temp AS (
select pv1.[prop_id],
       pv1.[assessed_val],
       ld1.[sale_id],
       sale1.[sl_price],
       sale1.[sl_dt],
       (pv1.[assessed_val]/NULLIF(sale1.[sl_price],0) as Ratio
  from dbo.land_detail as ld1 with(nolock)
  join dbo.property_val as pv1 with(nolock)
    on pv1.[prop_id] = ld1.[prop_id]
   and pv1.[prop_val_yr] = ld1.[prop_val_yr]
   and pv1.[sup_num] = ld1.[sup_num]
  left join dbo.sale as sale1 with(nolock)
    on sale1.[chg_of_owner_id] = ld1.[sale_id]
 where sale1.sl_dt <= '04/30/16'
   and sale1.sl_dt >= '05/01/15'
   and pv1.[sub_type] = 'r'
   and pv1.[prop_val_yr] = 2016
   and pv1.[prop_inactive_dt] is null
 order by sale_id, prop_id
)

SELECT STUFF(( SELECT ', ' + CAST(prop_id AS VARCHAR)
                FROM temp
                WHERE sale_id = t.sale_id
                FOR XML PATH(''),TYPE)
                .value('.','NVARCHAR(MAX)'),1,2,'') AS parcels,
       SUM(t.assessed_val) assessed_val,
       t.sale_id,
       t.sl_price,
       t.sl_dt,
       SUM(t.ratio) ratio
  FROM temp t
 GROUP BY t.sale_id,
          t.sl_price,
          t.sl_dt
 ORDER BY t.sale_id DESC

edited Aug 03 '17 at 22:54

answered Aug 03 '17 at 21:45

Ferdinand Gaspar

2,043
1
8
17

When I go to create the temp table it gives me this error: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. The statement has been terminated. – D. Hagen Aug 03 '17 at 22:15
And when I try and execute the second part I get this error message: Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value ', ' to data type int. – D. Hagen Aug 03 '17 at 22:17
I edited the code above to use WITH instead of INTO #temp. Can you provide your table structure to show the data type of each column. I think prop_id is of data type INT? – Ferdinand Gaspar Aug 03 '17 at 22:22
prop_id is of data type INT assessed_val is of data type numeric(14,0) and sale_id is of data type INT – D. Hagen Aug 03 '17 at 22:40
I edited the SQL code to use CAST(prop_id AS VARCHAR) – Ferdinand Gaspar Aug 03 '17 at 22:45
I am now getting an error code of... Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. Warning: Null value is eliminated by an aggregate or other SET operation. P.S. Thank you so much for your help so far! – D. Hagen Aug 03 '17 at 22:49
Divide by zero is encountered in getting the value for ratio from your original query. You may use NULLIF() like (pv1.[assessed_val]/NULLIF(sale1.[sl_price],0) as Ratio – Ferdinand Gaspar Aug 03 '17 at 22:55

Combining data into a single cell while summing a seperate cell

2 Answers2