tsql puzzle IIF

• tcronin 95651

  SSCertifiable

  Points: 6735

  More actions

  August 3, 2015 at 1:13 pm

  #310525

  I have the statement below (comes out of a temp table so I have some options here) ANytime I have a record which every field is the same but the user_name_desc may be different, I would like to concatenate those fields.

  ANd display only one distinct record. So instead of 2 rows I get on but the user_name_desc with a concatenated name.

  select

  distinct

  #FA4AG631HQ9D.task_id,practice_name, source_type_desc, source_nbr, name, location_name, task_type_desc, task_value, subject,

  start_date

  as start_date, due_date, expiration_date, followup_date,

  completion_date, completion_reason_desc, status_desc, priority_desc, task_origin, user_name_desc, completed_by_user_name, financial_class_desc, payer_name,

  source_amt, creator_name, create_timestamp, signature_name, modify_timestamp, #FA4AG631HQ9D.practice_id, source1_id, source2_id, source3_id, source_type,

  task_subgrouping1_desc, task_subgrouping2_desc,fileassigned,first_status_changedt from #FA4AG631HQ9D WHERE #FA4AG631HQ9D.practice_id = '0001'

  order by practice_name, source_type_desc, task_id

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 3, 2015 at 1:44 pm

  #1816952

  Here's an example on how you can do it, you might need to adapt the code as necessary. This article explains the method used: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

  SELECT DISTINCT

  task_id,

  practice_name,

  source_type_desc,

  source_nbr,

  name,

  location_name,

  task_type_desc,

  task_value,

  subject,

  start_date,

  due_date,

  expiration_date,

  followup_date,

  completion_date,

  completion_reason_desc,

  status_desc,

  priority_desc,

  task_origin,

  user_name_desc = STUFF((SELECT ', ' + user_name_desc

  FROM #FA4AG631HQ9D i

  WHERE f.task_id = i.task_id --Add as many conditions as necessary

  FOR XML PATH(''),TYPE).value('.','varchar(max)'), 1, 2, ''),

  completed_by_user_name,

  financial_class_desc,

  payer_name,

  source_amt,

  creator_name,

  create_timestamp,

  signature_name,

  modify_timestamp,

  practice_id,

  source1_id,

  source2_id,

  source3_id,

  source_type,

  task_subgrouping1_desc,

  task_subgrouping2_desc,

  fileassigned,

  first_status_changedt

  FROM #FA4AG631HQ9D f

  WHERE practice_id = '0001'

  ORDER BY practice_name, source_type_desc, task_id

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2