SQL Server 2000 - Combine data from several rows into one
Well, I'm not sure how easily this can be done so seek help from the collective wisdom.I have a query that outputs phone contact info. Each resulting row may have the same person but the phone type and number is different on each row. I need to be able to run a query that will have the resulting output in a single row with each row having the name and different phone types ( home, work, cell) as column headings. I was wondering what SQL functions might help me attain this part. Here is the query.
SELECT cn.Sno,Student.SID, Student.LName, Student.FName, Student.Grade, substring(cn.ContactName,1
FROM Student join StudentContactNumbers cn on cn.sno=Student.sno join StudentHomeInfo hi on hi.sno = Student.snojoin Household h on h.hhid = hi.hhid
where Student.Status = 'A' and cn.StudentRelation <>'self' and student.SNO=1920
ORDER by Student.Grade, Student.Lname
The output from above and what I need it to look like are in the attachment.
Thanks
Stude-Parent-ex.xlsx
SELECT cn.Sno,Student.SID, Student.LName, Student.FName, Student.Grade, substring(cn.ContactName,1
FROM Student join StudentContactNumbers cn on cn.sno=Student.sno join StudentHomeInfo hi on hi.sno = Student.snojoin Household h on h.hhid = hi.hhid
where Student.Status = 'A' and cn.StudentRelation <>'self' and student.SNO=1920
ORDER by Student.Grade, Student.Lname
The output from above and what I need it to look like are in the attachment.
Thanks
Stude-Parent-ex.xlsx
ASKER
Well, First, the hardware is new and it is virtualized. Secondly, this process is for moving our legacy SIS from in house to a new cloud based system. No more SQL 2000.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well I attached the output of the query along with the original (made a mistake on the original; Home number for mom and dad are the same). As you can see, the output for mom and dad is incorrect. They should have values in cell and home (home should be the same there) and work should have work numbers. Does "MAX" work properly if there are values in all the fields or the values are the same?
Stude-Parent-ex2.xlsx
Stude-Parent-ex2.xlsx
Don't know that. Posting a concise and complete example would help. Include table DDL - preferably as table variables - and sample data INSERT statements as one runnable T-SQL script.
Then the first thing you need to do is to get a new server.