Create Mailing List from Query Results

• lmarkum

  SSCertifiable

  Points: 7989

  More actions

  July 2, 2018 at 8:04 am

  #407029

  I have a query below which gets data for a mailing list to be generated.  What I need to know is how I can manipulate this result set into an actual mailing list.  My result should eventually look like this for each row:

  Jim Bob
  Bob's Automotive
  9000 Bob Drive
  Some City, OK 99999

  I think I probably need to add an IDENTITY to each row, but I'm not sure what to do after that. 

  
SELECT
   CASE
     WHEN [CntFirst] + ' ' + [CntLast] IS NULL THEN [CntTitle]
     WHEN LEN([CntFirst] + ' ' + [CntLast]) = 0 THEN [CntTitle]
     WHEN LEN([CntLast]) = 0 THEN [CntTitle]
     WHEN [CntLast] = '.' THEN [CntTitle]
     ELSE [CntFirst] + ' ' + [CntLast]
     END AS Recipient
  ,[CustName]
  ,[CustAdd]
  ,[CustCity]
  ,[CustState]
  ,[CustZip]
FROM [MyDB].[dbo].[Myview]


  Jim Bob	Bob's Automative	9000 Bob Drive	Some City	OK	99999
  Lee Markum	Markum Tire and Battery	1234 Markum Drive	Another City	TX	88888

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  July 3, 2018 at 6:56 pm

  #1996239

  lmarkum - Monday, July 2, 2018 8:04 AM

  I have a query below which gets data for a mailing list to be generated.  What I need to know is how I can manipulate this result set into an actual mailing list.  My result should eventually look like this for each row:

  Jim Bob
  Bob's Automotive
  9000 Bob Drive
  Some City, OK 99999

  I think I probably need to add an IDENTITY to each row, but I'm not sure what to do after that. 

  
SELECT
   CASE
     WHEN [CntFirst] + ' ' + [CntLast] IS NULL THEN [CntTitle]
     WHEN LEN([CntFirst] + ' ' + [CntLast]) = 0 THEN [CntTitle]
     WHEN LEN([CntLast]) = 0 THEN [CntTitle]
     WHEN [CntLast] = '.' THEN [CntTitle]
     ELSE [CntFirst] + ' ' + [CntLast]
     END AS Recipient
  ,[CustName]
  ,[CustAdd]
  ,[CustCity]
  ,[CustState]
  ,[CustZip]
FROM [MyDB].[dbo].[Myview]


  Jim Bob	Bob's Automative	9000 Bob Drive	Some City	OK	99999
  Lee Markum	Markum Tire and Battery	1234 Markum Drive	Another City	TX	88888

  It's really a display issue from the question but how would the results get to a letter or a mailing label? This might be something better done doing something like creating mailing labels with Word/Mail Merge or SSRS.

  Sue