CODEWITHSUNDEEP
sql
Hiral Nayak
Hiral Nayak

Reputation: 1072

Set Duplicate Row as One Row AND one Different Column Value Merge

I have ONE table like this enter image description here

in this table orderID column is repeat but its CollectDate is different

So i want to SET MY table AS means IF OrderID Same then concatenate Collect Date

enter image description here

MY query is following and its work 

            SELECT distinct df.OrderId as OrderId,df.FileName as FileName, df.RandomKey as RandomKey,      
            df.ClientId as ClientId, df.ProjectId as ProjectId, df.Status as Status,df.UserId as UserId,       
            df.emailTo as emailTo,df.PackageType as PackageType,df.RequestedDatetime as RequestedDatetime,
            STUFF(
            (SELECT '  |  ' +  convert(varchar(10),ord.CollectDate, 101)                                                

            FROM SMXPSU.OrderDetails ord
            WHERE df.OrderId =ord.OrderId --and ord.OrderId in(getdate()-30) 
            FOR XML PATH('')),1,1,'') AS CollectDate
            FROM SMXPSU.downloadfiles  AS df                  
            INNER Join
              SMXPSU.OrderDetails ord (NOLOCK) 
            ON df.OrderId=ord.OrderId    
            WHERE df.ClientId='shaw22'  and df.PackageType='Hard Copy' and df.RequestedDatetime>=getdate()-30 
            GROUP BY df.OrderId,FileName,PackageType,RequestedDatetime,RandomKey,ClientId,ProjectId,Status,UserId,emailTo,PackageType,RequestedDatetime,CollectDate

            order by df.OrderId desc ![enter image description here][3]

But Output give me like this

enter image description here

Means if CollectDate same then cumming multiple time but i want only one time if same

Upvotes: 0

Views: 551

Answers (2)

Kiran Hegde
Kiran Hegde

Reputation: 3681

You can try the following query

;WITH order_cte
AS
(
    SELECT OrderId,RandomKey,PackageType,RequestedDate,
    (
        SELECT CAST(CAST(orders.CollectedData AS DATE) AS VARCHAR)  + '-' AS 'text()'   
        FROM Orders WHERE Orders.OrderId=o.OrderId 
        FOR XML Path('')
    ) [CollectDate]
    FROM Orders o
    GROUP BY OrderId,RandomKey,PackageType,RequestedDate
)
SELECT OrderId,RandomKey,PackageType,RequestedDate,LEFT([CollectDate],LEN([CollectDate])-1) AS [CollectDate] 
FROM order_cte;

Upvotes: 1

user3815462
user3815462

Reputation: 61

SELECT OrderId, RandomKey, PackageType, RequestedDate, 
CASE 
WHEN COUNT(*)=1 THEN CAST(MIN(CollectDate) AS VARCHAR(20))
ELSE CAST(MIN(CollectDate) AS VARCHAR(20)) + '-' + CAST(MAX(CollectDate) AS VARCHAR    (20)) AS CollectDate
FROM
Table
GROUP BY OrderId, RandomKey, PackageType, RequestedDate

Upvotes: 1

Related Questions