Sql Group by to merge rows. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Sql Group by to merge rows.

Author

Topic

amerbashoeb
Starting Member

14 Posts

Posted - 2013-05-28 : 23:46:07

Hi there,

Following is my current output but this is not what i am after.

ProjectName SellingAgents HoldLotNo EOILotNo CILotNo
Little Creek Craig Truslove 582 NULL NULL
Little Creek Craig Truslove 577 NULL NULL
Little Creek Craig Truslove NULL 439 NULL
Little Creek Craig Truslove NULL NULL 601
Little Creek Craig Truslove NULL NULL 575

I am after the following structure/output

ProjectName SellingAgents HoldLotNo EOILotNo CILotNo
Little Creek Craig Truslove 582 439 601
Little Creek Craig Truslove 577 NULL 575
Little Creek Craig Truslove NULL NULL NULL
Little Creek Craig Truslove NULL NULL NULL
Little Creek Craig Truslove NULL NULL NULL

Could any one give me some ideas on how to resolve this. I have used Joins, Unions, Cases but none of them are helping me get that output.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-28 : 23:59:07

Can you post your table definitions, query and input data.

amerbashoeb
Starting Member

14 Posts

Posted - 2013-05-29 : 00:04:04

Following is my query.

DECLARE @SalesSummary TABLE (
ProjectName VarChar(500),
SellingAgents VarChar(500),
SaleStatusID Int,
StatusModifiedDate SmallDateTime,
PropertyStage VarChar(500),
LotNo VarChar(50),
FallenOver Bit,
StartDate SmallDateTime,
EndDate SmallDateTime
)

INSERT @SalesSummary (
ProjectName
,SellingAgents
,SaleStatusID
,StatusModifiedDate
,PropertyStage
,LotNo
,FallenOver
,StartDate
,EndDate
)

Select PrjProjectName
,dbo.GetSellingAgents(PLC.PropertyLifecycleGuid) As SellingAgents
,PLCSH.SaleStatusID
,PLCSH.CreatedDate as StatusModifiedDate
,PS.Name As PropertyStage
,LotNo
,FallenOver
,@StartDate
,@EndDate

From Project P
Inner Join PropertiesMore PM On PM.PrjId = P.PrjId
Inner Join PropertyLifeCycle PLC On PLC.PropertyCode = PM.Property_Code And PLC.AgencyCode = PM.Agency_Code
Inner Join PropertyStage PS On PS.PropertyStageID = PM.PropertyStageID
Inner Join (
SELECT PropertyCode, SaleStatusID, CreatedDate
FROM (SELECT PropertyCode, SaleStatusID, CreatedDate,
ROW_NUMBER() OVER (PARTITION BY PropertyCode
ORDER BY CreatedDate DESC)
AS RowNumber
FROM PropertyLifeCycleStatusHistory
Where CreatedDate Between @StartDate And @EndDate
--And SaleStatusID Not In (1,5,6)
) AS a
WHERE a.RowNumber = 1
)PLCSH On PLCSH.PropertyCode = PLC.PropertyCode
Where P.prjIsActive = 1
--And PLC.PropertySaleStatusID Not In (1,5,6)
And PM.IsDeleted = 0
And P.PrjId = @projects
And PLC.DateLastModified Between @StartDate And @EndDate
And dbo.GetSellingAgents(PLC.PropertyLifecycleGuid) Is Not Null

Select Distinct SS.ProjectName
,SS.SellingAgents
--Hold
,Hold.LotNo As HoldLotNo
,EOI.LotNo As EOILotNo
,CI.LotNo As CILotNo
From @SalesSummary SS
Left Join (
Select Distinct LotNo
,COUNT(*) OVER(PARTITION BY SellingAgents,ProjectName) AS [Count]
,COUNT(*) OVER(PARTITION BY ProjectName) AS TotalCount
,SellingAgents
,ProjectName
,SaleStatusID
From @SalesSummary
Where SaleStatusID = 2
And FallenOver = 0
And PropertyStage = 'Listed'
) Hold On Hold.LotNo = SS.LotNo
--Hold.SellingAgents = SS.SellingAgents And Hold.ProjectName = SS.ProjectName And Hold.SaleStatusID = SS.SaleStatusID
Left Join (
Select Distinct LotNo
,COUNT(*) OVER(PARTITION BY SellingAgents,ProjectName) AS [Count]
,COUNT(*) OVER(PARTITION BY ProjectName) AS TotalCount
,SellingAgents
,ProjectName
,SaleStatusID
From @SalesSummary
Where SaleStatusID = 3
And FallenOver = 0
And PropertyStage = 'Listed'
) EOI On EOI.LotNo = SS.LotNo
--EOI.SellingAgents = SS.SellingAgents And EOI.ProjectName = SS.ProjectName And EOI.SaleStatusID = SS.SaleStatusID
Left Join (
Select Distinct LotNo
,COUNT(*) OVER(PARTITION BY SellingAgents,ProjectName) AS [Count]
,COUNT(*) OVER(PARTITION BY ProjectName) AS TotalCount
,SellingAgents
,ProjectName
,SaleStatusID
From @SalesSummary
Where SaleStatusID = 4
And FallenOver = 0
And PropertyStage = 'Listed'
) CI On CI.LotNo = SS.LotNo
--CI.SellingAgents = SS.SellingAgents And CI.ProjectName = SS.ProjectName And CI.SaleStatusID = SS.SaleStatusID
Group By SS.ProjectName
,SS.SellingAgents
,Hold.LotNo
,EOI.LotNo
,CI.LotNo

Order By SS.ProjectName asc
,SS.SellingAgents asc
,Hold.LotNo desc
,EOI.LotNo desc
,CI.LotNo desc

amerbashoeb
Starting Member

14 Posts

Posted - 2013-05-29 : 00:07:29

I cannot even handle this at the application level because I am using ActiveReports and its really an unproductive product.

amerbashoeb
Starting Member

14 Posts

Posted - 2013-05-29 : 01:11:30

nobody?...seriously?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-29 : 01:16:37

You've got no responses as you've not explained your rules for getting the output. For example I see a value of 570
for CLotNo in sample data but thats not even there in final output
SO explain in words how you think you'll get the required output from the posted data.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

amerbashoeb
Starting Member

14 Posts

Posted - 2013-05-29 : 01:29:19

Apologies for that.

Following is my query.

My current out is like this. Please run the following block.

DECLARE @Temp TABLE (
ProjectName VarChar(500),
SellingAgents VarChar(500),
HoldLotNo VarChar(50),
EOILotNo VarChar(50),
CILotNo VarChar(50)
)
Insert into @temp (ProjectName,SellingAgents,HoldLotNo,EOILotNo,CILotNo)
values ('P1','Agent1','10',null,null)
Insert into @temp (ProjectName,SellingAgents,HoldLotNo,EOILotNo,CILotNo)
values ('P1','Agent1','11',null,null)
Insert into @temp (ProjectName,SellingAgents,HoldLotNo,EOILotNo,CILotNo)
values ('P1','Agent1',null,'13',null)
Insert into @temp (ProjectName,SellingAgents,HoldLotNo,EOILotNo,CILotNo)
values ('P1','Agent1',null,'45',null)
Insert into @temp (ProjectName,SellingAgents,HoldLotNo,EOILotNo,CILotNo)
values ('P1','Agent1',null,null,'109')

select * from @Temp

I want my out to be like this.

DECLARE @Temp2 TABLE (
ProjectName VarChar(500),
SellingAgents VarChar(500),
HoldLotNo VarChar(50),
EOILotNo VarChar(50),
CILotNo VarChar(50)
)
Insert into @temp2 (ProjectName,SellingAgents,HoldLotNo,EOILotNo,CILotNo)
values ('P1','Agent1','10','13','109')
Insert into @temp2 (ProjectName,SellingAgents,HoldLotNo,EOILotNo,CILotNo)
values ('P1','Agent1','11','45',null)

select * from @Temp2

amerbashoeb
Starting Member

14 Posts

Posted - 2013-05-29 : 01:32:29

I want the values for HoldLotNo, EOILotNo and CILotNot to be arrange at the top level for Agent1.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-29 : 01:54:14

[code]
;With CTE
AS
(SELECT ProjectName,SellingAgents,
CASE WHEN HoldLotNo IS NULL THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY ProjectName,SellingAgents ORDER BY HoldLotNo) AS SeqHoldLotNo,
CASE WHEN EOILotNo IS NULL THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY ProjectName,SellingAgents ORDER BY EOILotNo) AS SeqEOILotNo,
CASE WHEN CILotNo IS NULL THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY ProjectName,SellingAgents ORDER BY CILotNo) AS SeqCILotNo
FROM @Temp
)

SELECT c1.ProjectName,c1.SellingAgents,c1.HoldLotNo,c2.EOILotNo,c3.CILotNo
FROM CTE c1
LEFT OUTER JOIN CTE c2
ON c2.ProjectName = c1.ProjectName
AND c2.SellingAgents = c1.SellingAgents
AND c2.SeqEOILotNo = c1.SeqHoldLotNo
AND c2.SeqEOILotNo IS NOT NULL
LEFT OUTER JOIN CTE c3
ON c3.ProjectName = c1.ProjectName
AND c3.SellingAgents = c1.SellingAgents
AND c3.SeqCILotNo = c1.SeqHoldLotNo
AND c3.SeqCILotNo IS NOT NULL
WHERE c1.SeqHoldLotNo IS NOT NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

amerbashoeb
Starting Member

14 Posts

Posted - 2013-05-29 : 02:11:43

THANKS! That resolved my issue. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-29 : 02:25:25

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources