March 31, 2009 at 1:31 pm
Trouble finding a solution to a problem. I want to pivot the results from the query below grouping on the a.c_custno field. The results would generate multiple rows per a.c_custno. I want to limit the results to 5 records per a.c_custno and there can be less than 5. I am running sql 2005 sp2 and have looked into Pivot but have not found where I could use a non aggregate field. Any help or possible solution is greatly appreciated.
select a.c_custno, a.a_fname1, a.a_lname1,
c.cl_clino, c.cl_fname1, c.cl_lname1, c.cl_incdt
from client c join agent a on c.c_custno = a.c_custno
where c.cl_clino in (select cl_clino from schedule)
and c.cl_cancdt is null
and c.cl_incdt >= '3/1/2009'
For help on getting help, follow the link below:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 31, 2009 at 1:41 pm
Can you provide a table definition, some sample data, and a sample of what you want the output to look like from the sample data?
I'm having a little trouble visualizing the end result from your description.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 31, 2009 at 2:08 pm
Ok I have included what I think you are asking for. I have also attached the Sample Data and Desired-Output in separate files.
Field definition
c_custno(char(5), not null)
a_fname1(char(20), not null)
a_lname1(char(20), not null)
cl_clino(char(7), not null)
cl_fname1(char(20), not null)
cl_lname1(char(20), not null)
cl_incdt(datetime, not null)
Sample Data
c_custno,a_fname1,a_lname1,cl_clino,cl_fname1,cl_lname1,cl_incdt
C0146,Linda,Mayfield,C764825,Mark C,Reutzel ,3/16/2009
C0146,Linda,Mayfield,C764829,Amanda E,White,3/16/2009
C7389,Mary E. ,Couser,C721935,Barbara ,Schenck ,3/5/2009
C7389,Mary E. ,Couser,C721934,Steven,Kerber,3/5/2009
C9266,Gene ,Millman ,C742953,Kim,Corozzi ,3/11/2009
DN514,Scottie ,Elwood,C779287,David,Busch,3/20/2009
DN802,Dennis,Austin,C785210,Sarah & William ,Weese,3/23/2009
DN802,Dennis,Austin,C785209,Bruce,Hallowell,3/23/2009
SW526,Carla Jones,"LeLaCheur, CRB",C764748,Richard & Kelly ,Vogel,3/16/2009
SW526,Carla Jones,"LeLaCheur, CRB",C764743,Lucas & Areona,Conrad,3/16/2009
SW526,Carla Jones,"LeLaCheur, CRB",C764744,Randy & Peggy ,Dye,3/16/2009
SW526,Carla Jones,"LeLaCheur, CRB",C764746,Kenneth & Holly ,Graham,3/16/2009
Desired Results
c_custno,a_fname1,a_lname1,cl_clino,cl_fname1,cl_lname1,cl_incdt,cl_clino,cl_fname2,cl_lname2,cl_incdt,cl_clino,cl_fname3,cl_lname3,cl_incdt,cl_clino,cl_fname4,cl_lname4,cl_incdt
C0146,Linda,Mayfield,C764825,Mark C,Reutzel ,3/16/2009,C764829,Amanda E,White,3/16/2009,,,,,,,,
C7389,Mary E. ,Couser,C721935,Barbara ,Schenck ,3/5/2009,C721934,Steven,Kerber,3/5/2009,,,,,,,,
C9266,Gene ,Millman ,C742953,Kim,Corozzi ,3/11/2009,,,,,,,,,,,,
DN514,Scottie ,Elwood,C779287,David,Busch,3/20/2009,,,,,,,,,,,,
DN802,Dennis,Austin,C785210,Sarah & William ,Weese,3/23/2009,C785209,Bruce,Hallowell ,3/23/2009,,,,,,,,
SW526,Carla Jones ,"LeLaCheur, CRB",C764748,Richard & Kelly ,Vogel,3/16/2009,C764743,Lucas & Areona,Conrad,3/16/2009,C764744,Randy & Peggy ,Dye,3/16/2009,C764746,Kenneth & Holly ,Graham,3/16/2009
For help on getting help, follow the link below:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 1, 2009 at 7:18 am
It looks like each customer number always has the same data for first and last name, just different data for first name 1, and last name 1. Is that correct?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 1, 2009 at 7:52 am
Correct. C_Custno (PK for Agent Table, FK for Client Table) A_Fname1 A_Lname1 - this is the grouping or parent
CL_Clino (PK for Client Table) CL_Fname1 CL_Lname1 CL_Incdt
CL_Clino (PK for Client Table) CL_Fname2 CL_Lname2 CL_Incdt
CL_Clino (PK for Client Table) CL_Fname3 CL_Lname3 CL_Incdt
CL_Clino (PK for Client Table) CL_Fname4 CL_Lname4 CL_Incdt
CL_Clino (PK for Client Table) CL_Fname5 CL_Lname5 CL_Incdt
Each Customer can have up to 5 Clients
For help on getting help, follow the link below:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 1, 2009 at 9:01 am
Never more than five?
If so, then something like this will work:
;with
Clients (ClientSeq, CustNum, ClientNum, ClientFName, ClientLName, ClientDate) as
(select
row_number() over (partition by c_custno order by cl_clino),
c_custno,
cl_clino,
cl_fname,
cl_lname,
cl_incdt
from dbo.MyTable)
Customers (CustNum, CustLast, CustFirst) as
(select distinct
c_custno,
a_fname1,
a_lname1
from dbo.MyTable)
select *
from Customers
left outer join Clients Clients1
on Customers.CustNum = Clients1.CustNum
and Clients1.ClientSeq = 1
left outer join Clients Clients2
on Customers.CustNum = Clients2.CustNum
and Clients2.ClientSeq = 2
left outer join Clients Clients3
on Customers.CustNum = Clients3.CustNum
and Clients3.ClientSeq = 3
left outer join Clients Clients4
on Customers.CustNum = Clients4.CustNum
and Clients4.ClientSeq = 4
left outer join Clients Clients5
on Customers.CustNum = Clients5.CustNum
and Clients5.ClientSeq = 5;
You'll need to give the correct table name (I didn't see it in your posts, but maybe I just overlooked it), and you'll need to clean up the final select so it has the column names you want, but those should be easy enough.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 1, 2009 at 9:33 am
That looks like it should work. I will test it and let you know if I have any issues.
Thank you so much for your help!
Update:
one syntax error. I added a comma between the end of the Clients Select and the beginning of the Customers Select in the With statement.
This is exactly what I was looking for and works perfectly! I knew there was genius here!
For help on getting help, follow the link below:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 1, 2009 at 2:23 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply