troubleshooting Question

Could you point a way to group all the contents of the resultant UNION column to just one result line by using MSSQLServer?

Avatar of Eduardo Fuerte
Eduardo FuerteFlag for Brazil asked on
Microsoft SQL Server
8 Comments1 Solution36 ViewsLast Modified:
Hi Experts

Could you point a way to group all the contents of the resultant UNION column to just one result line by using MSSQLServer?

Accordingly to:

select top (1000)
   tbl_x.cIDOrder,
   tbl_x.cIDCompany,
   tbl_x.cCustomer,
   tbl_x.cIDCustomer,
   tbl_x.cName,
   tbl_x.cTerritoryName,
   tbl_x.cCondPagto,
   tbl_x.dCreated,
   tbl_x.dShipping,
   tbl_x.cCommunicationValue,  -- <<<<   All the cCommunicationValue(s) agruped at just one column
   tbl_x.nOrderTotalValue,
   tbl_x.cType,
   tbl_x.cPDFTitle,
   tbl_x.cBodyText,
   

from
(
    
   SELECT DISTINCT
         O.cIDOrder AS cIDOrder,
         O.cIDCompany AS cIDCompany,
         O.cIDCustomer + ' - ' + C.cCustomerName AS cCustomer,
         O.cIDCustomer AS cIDCustomer,
         C.cCustomerName AS cName,
         T.cTerritoryName AS cTerritoryName,
         GD_CP.cDescription AS cCondPagto,
         O.dCreated AS dCreated,
         O.dExpectedBilling AS dShipping,
         C1.cCommunicationValue AS cCommunicationValue, -- <<<<============
         O.nTotalValue AS nOrderTotalValue,
         'PEDIDO' AS cType,
         'Relatório do Pedido' AS cPDFTitle,
         'cópia do Pedido' AS cBodyText 
      FROM
         ...
      UNION
      SELECT DISTINCT
         O.cIDOrder AS cIDOrder,
         O.cIDCompany AS cIDCompany,
         O.cIDCustomer + ' - ' + C.cCustomerName AS cCustomer,
         O.cIDCustomer AS cIDCustomer,
         C.cCustomerName AS cName,
         T.cTerritoryName AS cTerritoryName,
         GD_CP.cDescription AS cCondPagto,
         O.dCreated AS dCreated,
         O.dExpectedBilling AS dShipping,
         US.cEmail AS cCommunicationValue,  -- <<<<============
         O.nTotalValue AS nOrderTotalValue,
         'PEDIDO' AS cType,
         'Relatório do Pedido' AS cPDFTitle,
         'cópia do Pedido' AS cBodyText 
      FROM
         ...
      UNION
      SELECT DISTINCT
         O.cIDOrder AS cIDOrder,
         O.cIDCompany AS cIDCompany,
         O.cIDCustomer + ' - ' + C.cCustomerName AS cCustomer,
         O.cIDCustomer AS cIDCustomer,
         C.cCustomerName AS cName,
         T.cTerritoryName AS cTerritoryName,
         GD_CP.cDescription AS cCondPagto,
         O.dCreated AS dCreated,
         O.dExpectedBilling AS dShipping,
         C1.cCommunicationValue AS cCommunicationValue, -- <<<<============
         O.nTotalValue AS nOrderTotalValue,
         'COTAÇÃO' AS cType,
         'Cotação' AS cPDFTitle,
         'cópia da Cotação' AS cBodyText 
      FROM
         ...

      UNION

      SELECT DISTINCT
            O.cIDOrder AS cIDOrder,
            O.cIDCompany AS cIDCompany,
            O.cIDCustomer + ' - ' + C.cCustomerName AS cCustomer,
            O.cIDCustomer AS cIDCustomer,
            C.cCustomerName AS cName,
            T.cTerritoryName AS cTerritoryName,
            GD_CP.cDescription AS cCondPagto,
            O.dCreated AS dCreated,
            O.dExpectedBilling AS dShipping,
            CO.cCommunicationValue as cCommunicationValue2, -- <<<<============
            O.nTotalValue AS nOrderTotalValue,
            'PEDIDO' AS cType,
            'Relatório do Pedido' AS cPDFTitle,
            'cópia do Pedido' AS cBodyText
         FROM
            ...
      ) tbl_x

Open in new window

The final query must to consider at just one column all the "emails" to send it by using just one email to all the recipient(s)

Thanks in advance
ASKER CERTIFIED SOLUTION
Eduardo Fuerte
Developer and Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 8 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 8 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004