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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy