multiples of 3

[simian336]

I have to match a preexisting report format that requies everthing to be in groups of 3, so....

LastName FirstName ConDate Amount
Jones Greg 3/7/2012 5
Kim Brent 2/22/2012 2
Kim Brent 3/7/2012 2
Kim Brent 2/22/2012 2
Law Danny 2/22/2012 5
Law Danny 3/7/2012 5
Law Danny 2/22/2012 5
Law Danny 3/7/2012 5

Expected Result

LastName FirstName ConDate Amount
Jones Greg 3/7/2012 5
Jones Greg
Jones Greg
Kim Brent 2/22/2012 2
Kim Brent 3/7/2012 2
Kim Brent 2/22/2012 2
Law Danny 2/22/2012 5
Law Danny 3/7/2012 5
Law Danny 2/22/2012 5
Law Danny 3/7/2012 5
Law Danny
Law Danny

Any ideas apprciated.

Simi

 

[gmmastros]

That's a rather odd request, but I suppose there are multiple ways to accomplish this. Here's one of them with a table variable and same data to demonstrate.

Declare @Temp Table(LastName VarChar(50), FirstName VarChar(50), ConDate DateTime, Amount int)

Insert Into @Temp Values('Mastros','George','3/7/2012',5)
Insert Into @Temp Values('Mastros','George','3/7/2012',5)
Insert Into @Temp Values('Jones','Greg','3/7/2012',5)
Insert Into @Temp Values('Kim','Brent','2/22/2012',2)
Insert Into @Temp Values('Kim','Brent','3/7/2012',2)
Insert Into @Temp Values('Kim','Brent','2/22/2012',2)
Insert Into @Temp Values('Law','Danny','2/22/2012',5)
Insert Into @Temp Values('Law','Danny','3/7/2012',5)
Insert Into @Temp Values('Law','Danny','2/22/2012',5)
Insert Into @Temp Values('Law','Danny','3/7/2012',5)

Select	1, LastName, FirstName, ConDate, Amount 
From    @Temp

Union All

Select 2, LastName, FirstName, NULL, NULL
From   @Temp
Group By LastName, FirstName
Having Count(*) % 3 <> 0

Union All

Select 3, LastName, FirstName, NULL, NULL
From   @Temp
Group By LastName, FirstName
Having Count(*) % 3 = 1

Order BY LastName, FirstName, ConDate

Note that I use Union All to add rows as necessary

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

George,
Neat trick with the padding of additional rows.

Simi,
This will match your sort order that you need for the report

* Order BY LastName, FirstName[red], 1[/red], ConDate

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)

 

[genomon]

There's an MVP in the house - simple elegance, George.

Beir bua agus beannacht!

 

[simian336]

Thanks George...

Simi