How to merge lines?

Question

How to merge lines?

Navigation

#1 by (2 votes)

0

How can I merge two or more **Linhas x Coluna** of the query below?

select cast(OS.CreateDate as date) as DtMovimentacao, OS.Id as NrOS, OS.Description Origem, OSI.Description as ItemSaida, OSI.Quantity as QtdSaida,
OSE.Description, OSE.Quantity, OSE.Notes
from Asset_WorkOrders OS
join Asset_WorkOrderOtherCost OSI
on OSI.WorkOrderId = OS.Id
join 
(
    select OS.WorkOrderId, OSI.Description,OSI.Quantity,OSI.Notes from Asset_WorkOrders OS
    join Asset_WorkOrderOtherCost OSI
    on OSI.WorkOrderId = OS.Id
    where TypeId = 56
)OSE
on OSE.WorkOrderId = OS.Id
where OS.Id = 147412
and OS.TypeId = 34

I needed the result to look like this.

This in query or through Report Server .

sql sql-server reporting-services

asked by anonymous 04.12.2015 / 17:43

1 answer

Like SQL with multiple fields [closed] Infinite list with ionic?

score 2 · Accepted Answer

EDIT: With SQL I think the only way is to check the information that is different between the rows and use FOR XML PATH in that column, in the example below I used in the "Description" column

select cast(OS.CreateDate as date) AS DtMovimentacao, OS.Id AS NrOS,
Origem = (SELECT OS2.Description + CHAR(10)
FROM Asset_WorkOrders OS2
WHERE OS.Id = OS2.Id
  and OS.TypeId = OS2.TypeId
FOR XML PATH(''))
OS.Description as Origem,

OSI.Description as ItemSaida, OSI.Quantity as QtdSaida,
OSE.Description, OSE.Quantity, OSE.Notes
from Asset_WorkOrders OS
join Asset_WorkOrderOtherCost OSI
on OSI.WorkOrderId = OS.Id
join 
(
    select OS.WorkOrderId, OSI.Description,OSI.Quantity,OSI.Notes from Asset_WorkOrders OS
    join Asset_WorkOrderOtherCost OSI
    on OSI.WorkOrderId = OS.Id
    where TypeId = 56
)OSE
on OSE.WorkOrderId = OS.Id
where OS.Id = 147412
and OS.TypeId = 34

As you have not included table information, you may have some inconsistency in the result of the Source column, in this case you must include some JOINs or validations in the WHERE in the subquery.