Link to home
Get AccessLog in
Avatar of Eduardo Fuerte
Eduardo FuerteFlag for Brazil

asked on

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

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
Avatar of lcohan
lcohan
Flag of Canada image

Depending on how big the UNION resultant record set is - you could put it in a CTE if that's not very big (up to few tens of thousands of rows) OR into a temporary table and then use that CTE or #temp table in a SELECT ...WHERE...GROUP BY. On temp table if you have millions of rows you could add index(es) to speed up the sorting/group by.. Should be something like:
with tbl_x (
   cIDOrder,
    cIDCompany,
    cCustomer,
    cIDCustomer,
    cName,
   cTerritoryName,
   cCondPagto,
   dCreated,
   dShipping,
   cCommunicationValue,  -- <<<<   All the cCommunicationValue(s) agruped at just one column
   nOrderTotalValue,
   cType,
   cPDFTitle,
   cBodyText)
AS 
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
            ...;

SELECT cIDOrder,
    cIDCompany,
    cCustomer,
    cIDCustomer,
    cName,
   cTerritoryName,
   cCondPagto,
   dCreated,
   dShipping,
   cCommunicationValue,  -- <<<<   All the cCommunicationValue(s) agruped at just one column
   nOrderTotalValue,
   cType,
   cPDFTitle,
   cBodyText
FROM tbl_x WHERE ....GROUP BY ....

--OR 

CREATE TABLE #tbl_x 
(--must add a datatype for each column below
   cIDOrder,
    cIDCompany,
    cCustomer,
    cIDCustomer,
    cName,
   cTerritoryName,
   cCondPagto,
   dCreated,
   dShipping,
   cCommunicationValue,  -- <<<<   All the cCommunicationValue(s) agruped at just one column
   nOrderTotalValue,
   cType,
   cPDFTitle,
   cBodyText
);
INSERT INTO #tbl_x
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
            ...;

SELECT cIDOrder,
    cIDCompany,
    cCustomer,
    cIDCustomer,
    cName,
   cTerritoryName,
   cCondPagto,
   dCreated,
   dShipping,
   cCommunicationValue,  -- <<<<   All the cCommunicationValue(s) agruped at just one column
   nOrderTotalValue,
   cType,
   cPDFTitle,
   cBodyText
FROM #tbl_x WHERE ....GROUP BY ....;

Open in new window

SELECT ... FROM table1
UNION
SELECT ... FROM table2

 adds rows to each other.

To join in one line:
SELECT table1.column AS column1, table2.column AS column2, ...
FROM table1 JOIN table2 ON table1.fk = table2.pk

but this join can produce more than one row as well.

To limit to 1 row you could:
SELECT TOP (1) SELECT ... FROM table1 JOIN table2 ON table1.fk = table2.pk

or you can use aggregation to reduce the number of rows:
SELECT ... FROM table1 JOIN table2 ON table1.fk = table2.pk
can produce a table:
Id, name, value
1, sss, 32
2, sss, 40
3, ddd, 15
4, ddd, 10
5, ddd, 25

One row:
SELECT Sum(value) AS sumValue FROM table
sumValue
122

or for every name:

SELECT name, sum(value) AS sumValue FROM table
GROUP BY name

name, sumValue
sss, 72
ddd, 50

you also can SELECT FROM SELECT:

SELECT TOP(1) FROM (
   SELECT ... JOIN ...
) t
WHERE t.colum = ...

Another approach is to use TABLE-VARIABLE
Convert
SELECT ... FROM table1 WHERE ...
UNION
SELECT ... FROM table2 WHERE ...

to
DECLARE @t1 TABLE (Id INT, ...)
DECLARE @t2 TABLE (Id INT, ...)
INSERT INTO @t1 (Id, ...) SELECT FROM table1 WHERE ...
INSERT INTO @t2 (Id, ...) SELECT FROM table2 WHERE ...
SELECT ... FROM @t1 t1 JOIN @t2 t2 ON t1.pk = t2.fk

In this way you can SELECT * FROM @t1/@t2 to analyze intermediate result to be sure that JOIN works as required.

It sounds as if you want a list of values in a single column. To do that, you use FOR XML PATH in MSSQL, but that requires to add a correlated subquery using the UNION result - something making it a even more complex query.
If you create a CTE for that UNION as-is, you can use a correlated subquery with a self join, it should be much easier.
The FOR XML PATH feature is documented e.g. at https://www.sqlshack.com/for-xml-path-clause-in-sql-server/, see Example 8. I would use the STUFF approach.

Overall schematic:
;with tbl_x as (
   select distinct ...
   union
   select distinct ..
 ...
)
select top (1000)
   cIDOrder,
   ...,
   STUFF(
    (
        SELECT ',' + cCommunicationValue
        FROM tbl_x T2
        WHERE T2.ID = T1.ID FOR XML PATH('')
    ), 1, 1, '') AS cCommunicationValue,
   ...
from tbl_x T1;

Open in new window

where T2.ID = T1.ID is just an example join condition.
Why do you need 1000 rows collapsed into single row?

By the way, TOP() really needs an ORDER BY clause to be used.
Avatar of Eduardo Fuerte

ASKER

Hi

Thank you for the replies.
Still studing it.

My purpose is to send just one email for all the involved.

Currently the email address column have one adress per line, so this column must have a comma separated list of emails.
Hi

Sorry the delay, someone else at company solved it that way:
 SELECT cIDorder, cidCompany, cCustomer, cIDCustomer, cName, cTerritoryName, cCondPagto, dCreated, dShipping, 
 cCommunicationValue,
 nOrderTotalValue, cType, cPDFTitle, cBodyText 
 INTO #temp 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
			MXX_Order O WITH (NOLOCK) 
			INNER JOIN
				MXX_OrderExt OE WITH (NOLOCK) 
				ON OE.cIDCompany = O.cIDCompany 
				AND OE.cIDOrder = O.cIDOrder 
			INNER JOIN
				MXX_Customer C WITH (NOLOCK) 
				ON C.cIDCompany = O.cIDCompany 
				AND C.cIDCustomer = O.cIDCustomer 
			INNER JOIN
				MXX_Territory T WITH (NOLOCK) 
				ON T.cIDCompany = O.cIDCompany 
				AND T.cIDTerritory = O.cIDTerritory 
			INNER JOIN
				MXX_GeneralDescription GD_CP 
				ON GD_CP.cIDCompany = O.cIDCompany 
				AND GD_CP.cDomainType = 'xIDPaymentCondition' 
				AND GD_CP.cIDDomainType = O.xIDPaymentCondition 
			INNER JOIN
				MXX_OrderItem OI WITH (NOLOCK) 
				ON OI.cIDCompany = O.cIDCompany 
				AND OI.cIDOrder = O.cIDOrder 
		   INNER JOIN
				 MXX_ProductLang P WITH (NOLOCK) 
				 ON P.cIDCompany = O.cIDCompany 
				 AND P.cIDProduct = OI.cIDProduct 
				 AND P.cIDLanguage = '1' 
			INNER JOIN
				MXX_ContactCommunication CC WITH (NOLOCK) 
				ON CC.cIDCompany = O.cIDCompany 
				AND CC.cIDCustomer = O.cIDCustomer 
				AND CC.xIDCommunicationType = '02' 
			INNER JOIN
				MXX_Communication C1 WITH (NOLOCK) 
				ON C1.cIDCompany = O.cIDCompany 
				AND C1.cIDCommunication = CC.cIDCommunication 
				AND C1.xIDCommunicationType = '02' 
				AND C1.MXXEnabled = 1 
				AND C1.cCommunicationValue IS NOT NULL 
				AND C1.cCommunicationValue != '' 
		WHERE
			1 = 1 
			AND OE.lMatrixOrder != 1 
			AND OE.lSendMail = 1 
			AND OE.dSendMail IS NULL 
			AND O.lQuotation = 0 
		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
			MXX_Order O WITH (NOLOCK) 
			INNER JOIN
				MXX_OrderExt OE WITH (NOLOCK) 
				ON OE.cIDCompany = O.cIDCompany 
				AND OE.cIDOrder = O.cIDOrder 
			INNER JOIN
				MXX_Customer C WITH (NOLOCK) 
				ON C.cIDCompany = O.cIDCompany 
				AND C.cIDCustomer = O.cIDCustomer 
			INNER JOIN
				MXX_Territory T WITH (NOLOCK) 
				ON T.cIDCompany = O.cIDCompany 
				AND T.cIDTerritory = O.cIDTerritory 
			INNER JOIN
				MXX_GeneralDescription GD_CP 
				ON GD_CP.cIDCompany = O.cIDCompany 
				AND GD_CP.cDomainType = 'xIDPaymentCondition' 
				AND GD_CP.cIDDomainType = O.xIDPaymentCondition 
			INNER JOIN
				MXX_OrderItem OI WITH (NOLOCK) 
				ON OI.cIDCompany = O.cIDCompany 
				AND OI.cIDOrder = O.cIDOrder 
			INNER JOIN
			   MXX_ProductLang P WITH (NOLOCK) 
			   ON P.cIDCompany = O.cIDCompany 
			   AND P.cIDProduct = OI.cIDProduct 
			   AND P.cIDLanguage = '1' 
			INNER JOIN
				MXX_UserTerritory UT WITH (NOLOCK) 
				ON UT.cIDCompany = O.cIDCompany 
				AND UT.cIDTerritory = O.cIDTerritory 
				AND UT.lOrigin = 1 
				AND ut.MXXEnabled = 1 
			INNER JOIN
				MXX_User US WITH (NOLOCK) 
				ON US.cIDCompany = UT.cIDCompany 
				AND US.cIDUser = UT.cIDUser 
				AND US.cEmail IS NOT NULL 
				AND US.cEmail != '' 
				AND US.MXXEnabled = 1 
			INNER JOIN
				MXX_UserProfileBusiness UP WITH (NOLOCK) 
				ON US.cIDCompany = UP.cIDCompany 
				AND US.cIDUser = UP.cIDUser 
				AND UP.cProfile = 'PreSales' 
				AND up.MXXEnabled = 1 
		WHERE
			1 = 1 
			AND OE.lMatrixOrder != 1 
			AND OE.dSendMail IS NULL 
			AND O.lQuotation = 0 
		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
			MXX_Order O WITH (NOLOCK) 
			INNER JOIN
				MXX_OrderExt OE WITH (NOLOCK) 
				ON OE.cIDCompany = O.cIDCompany 
				AND OE.cIDOrder = O.cIDOrder 
				AND OE.cIDQuotation IS NULL 
			INNER JOIN
				MXX_Customer C WITH (NOLOCK) 
				ON C.cIDCompany = O.cIDCompany 
				AND C.cIDCustomer = O.cIDCustomer 
			INNER JOIN
				MXX_Territory T WITH (NOLOCK) 
				ON T.cIDCompany = O.cIDCompany 
				AND T.cIDTerritory = O.cIDTerritory 
			INNER JOIN
				MXX_GeneralDescription GD_CP 
				ON GD_CP.cIDCompany = O.cIDCompany 
				AND GD_CP.cDomainType = 'xIDPaymentCondition' 
				AND GD_CP.cIDDomainType = O.xIDPaymentCondition 
			INNER JOIN
				MXX_OrderItem OI WITH (NOLOCK) 
				ON OI.cIDCompany = O.cIDCompany 
				AND OI.cIDOrder = O.cIDOrder 
			INNER JOIN
				MXX_ProductLang P WITH (NOLOCK) 
				ON P.cIDCompany = O.cIDCompany 
				AND P.cIDProduct = OI.cIDProduct 
			   AND P.cIDLanguage = '1' 
			INNER JOIN
				MXX_ContactCommunication CC WITH (NOLOCK) 
				ON CC.cIDCompany = O.cIDCompany 
				AND CC.cIDCustomer = O.cIDCustomer 
				AND CC.xIDCommunicationType = '02' 
			INNER JOIN
				MXX_Communication C1 WITH (NOLOCK) 
				ON C1.cIDCompany = O.cIDCompany 
				AND C1.cIDCommunication = CC.cIDCommunication 
				AND C1.xIDCommunicationType = '02' 
				AND C1.MXXEnabled = 1 
				AND C1.cCommunicationValue IS NOT NULL 
				AND C1.cCommunicationValue != '' 
			LEFT JOIN
				MXX_OrderExt oe1 
				ON oe1.cIDCompany = o.cIDCompany 
				AND oe1.cIDQuotation = o.cIDOrder 
		WHERE
			1 = 1 
			AND OE.lSendMail = 1 
			AND OE.dSendMail IS NULL 
			AND O.lQuotation = 1 
			AND oe1.cIDOrder IS NULL 

		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
				MXX_Order O WITH (NOLOCK) 
				INNER JOIN
					MXX_OrderExt OE WITH (NOLOCK) 
					ON OE.cIDCompany = O.cIDCompany 
					AND OE.cIDOrder = O.cIDOrder 
				INNER JOIN
					MXX_Customer C WITH (NOLOCK) 
					ON C.cIDCompany = O.cIDCompany 
					AND C.cIDCustomer = O.cIDCustomer 
				LEFT JOIN 
					MXX_Communication CO WITH(NOLOCK)
					ON CO.cIDCustomer = C.cIDCustomer
					AND CO.cIDCompany = C.cIDCompany
				INNER JOIN
					MXX_Territory T WITH (NOLOCK) 
					ON T.cIDCompany = O.cIDCompany 
					AND T.cIDTerritory = O.cIDTerritory 
				INNER JOIN
					MXX_GeneralDescription GD_CP 
					ON GD_CP.cIDCompany = O.cIDCompany 
					AND GD_CP.cDomainType = 'xIDPaymentCondition' 
					AND GD_CP.cIDDomainType = O.xIDPaymentCondition 
				INNER JOIN
					MXX_OrderItem OI WITH (NOLOCK) 
					ON OI.cIDCompany = O.cIDCompany 
					AND OI.cIDOrder = O.cIDOrder 
				INNER JOIN
					MXX_ProductLang P WITH (NOLOCK) 
					ON P.cIDCompany = O.cIDCompany 
					AND P.cIDProduct = OI.cIDProduct 
					AND P.cIDLanguage = '1' 
				INNER JOIN
					MXX_UserTerritory UT WITH (NOLOCK) 
					ON UT.cIDCompany = O.cIDCompany 
					AND UT.cIDTerritory = O.cIDTerritory 
					AND UT.lOrigin = 1 
					AND ut.MXXEnabled = 1 
			WHERE
				OE.lMatrixOrder != 1 
				AND O.lQuotation = 0 	
				AND OE.lSendMail = 1 	
				AND OE.dSendMail IS NULL
)A


select DISTINCT cIDorder, cidCompany, cCustomer, cIDCustomer, cName, cTerritoryName, cCondPagto, dCreated, dShipping, 
 LEFT((Select cCommunicationValue + ',' From #temp B Where A.cidorder = B.cidorder FOR XML PATH('')),LEN((Select cCommunicationValue + ',' From #temp B Where A.cidorder = B.cidorder FOR XML PATH('')))-1) as cCommunicationValue,
 nOrderTotalValue, cType, cPDFTitle, cBodyText 
 From #temp A 

Open in new window

My last post solved the question.
ASKER CERTIFIED SOLUTION
Avatar of Eduardo Fuerte
Eduardo Fuerte
Flag of Brazil image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access