• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 49
  • Last Modified:

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
0
smantz
Asked:
smantz
  • 3
  • 2
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now