October 3, 2013 at 9:52 am
Hi, sorry about long thread but here we go, so i have been trying to figure this piece out as well as search forms and i find similar questions but not the exact one. The issue is that i have a raw data table which extracts from a legacy application, the raw data comes as follows
car_id: 1
car_name: vw
customer_comment: oil change
dealer_comment: complete
response_line: 1
car_id:1
car_name: vw
customer_comment: brakes change
dealer_comment: NULL
response_line: 2
note that this response_line field is breaking up a "text area" by line number (yes that legacy application can't just extract the text area it as a whole). i'm tying to append the dealer_comment into 1 field so i can update a staging table and clean things up...the perfect output would be
car_id:1
car_name: vw
customer_comment: oil change brakes change
dealer_comment: complete
here is my code which works (in two different ways) but what happens is the code doesn't include any records that exactly match the number response_lines (there can be a max of 3), example if one record has 3 response_lines (3 lines filled in the text area) this works and another record has 2 response_line (only 2 line filled in the text area) this code would not include the 2 response_line record
;with temp (num1,t1)as
(
select car_id, dealer_comment
from cars
where Response_Line ='1'
and cars.query='dealer_comment'
),
temp2 (num2,t2)as
(
select car_id, dealer_comment
from cars
where Response_Line ='2'
and cars.query='dealer_comment'
),
temp3 (num3,t3)as
(
select car_id,dealer_comment
from cars
where Response_Line ='3'
and cars.query='dealer_comment'
)
select t1+' '+t2+' '+t3 from temp
inner join temp2
on temp.num1=temp2.num2
*shows all records with only 3 response lines* i was hoping to show all records
or
update staging.cars
set dealer_comments=
(select dealer_comment from cars
where Response_Line = '1' and cars.query='dealer_comment'
and staging.cars.car_id = cars.car_id
)
+ ' ' +
(select Response_Text from cars
where Response_Line = '2' and cars.query='dealer_comment'
and staging.cars.car_id = cars.car_id
) + ' ' +
(select Response_Text from cars
where Response_Line = '3' and cars.query='dealer_comment'
and staging.cars.car_id = cars.car_id
)
*updates records with only 3 response lines
my plan would be to update based on either method, either CTE tables or a update and i would certainly understand i need to incorporate a case when else but i can't' seem to figure that piece out.
i hope this all makes sense and any advice would be appreciated
October 3, 2013 at 12:30 pm
update so issue is resolved sorry about the complex question wasn't sure how to word it however answer is below for those looking..
SELECT
TBL1.car_id,
STUFF((
SELECT ',' + TBL2.customer_comment
FROM staging.cars AS TBL2
WHERE TBL1.car_id = TBL2.car_id
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS Customer_Comments
FROM staging.cars AS TBL1
GROUP BY TBL1.car_id;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply