Create Mailing List from Query Results

  • lmarkum

    SSCertifiable

    Points: 7771

    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: 89885

    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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply