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,22) as Contact, cn.StudentRelation as Relation, cn.PhoneTitle, cn.Number
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
smantzDirector of TechnologyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
I really don't know how to put that into friendly words: SQL Server 2000?? Really?

Then the first thing you need to do is to get a new server.
0
smantzDirector of TechnologyAuthor Commented:
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.
0
ste5anSenior DeveloperCommented:
+1 :)

You need to pivot it. But you know that this is not normalized?

First you need to clean the phone title column. This should work, when each type occurs only once:

SELECT  Q.Sno,
        Q.SID,
        Q.LName,
        Q.FName,
        Q.Grade,
        Q.Contact,
        Q.Relation,
        CASE
            WHEN MAX(Q.PhoneType) = 'Cell'
            THEN MAX(Q.Number)
            ELSE NULL
        END AS Cell,
        CASE
            WHEN MAX(Q.PhoneType) = 'Home'
            THEN MAX(Q.Number)
            ELSE NULL
        END AS Home,
        CASE
            WHEN MAX(Q.PhoneType) = 'Work'
            THEN MAX(Q.Number)
            ELSE NULL
        END AS Work
FROM    (   SELECT  CN.Sno,
                    S.SID,
                    S.LName,
                    S.FName,
                    S.Grade,
                    SUBSTRING(CN.ContactName,1,22) AS Contact,
                    CN.StudentRelation AS Relation,
                    CASE
                        WHEN CN.PhoneTitle LIKE '%cell%' THEN 'Cell'
                        WHEN CN.PhoneTitle LIKE '%home%' THEN 'Home'
                        WHEN CN.PhoneTitle LIKE '%work%' THEN 'Work'
                        ELSE '<n/a>'
                    END AS PhoneType
                    CN.Number
            FROM    Student S
                INNER JOIN StudentHomeInfo HI ON HI.sno = S.sno
                INNER JOIN Household H ON H.hhid = HI.hhid
                INNER JOIN StudentContactNumbers CN ON CN.sno=S.sno
            WHERE   S.Status = 'A'
                AND CN.StudentRelation <>'self'
                AND S.SNO=1920
        ) Q
GROUP BY Q.Sno,
    Q.SID,
    Q.LName,
    Q.FName,
    Q.Grade,
    Q.Contact,
    Q.Relation;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
smantzDirector of TechnologyAuthor Commented:
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
0
ste5anSenior DeveloperCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.