Answer

SQL PIVOT helps merge results into a single row

I have a query that returns 10 rows and 10 columns. Person_ID is the primary key. The result set may include the person_ID, name and telephone. For each person_ID there are 15 organizations, and I would like all the organizations combined into a single row. The result would include name, city, telephone, and then Org1, Org2, Org3, etc. Can you help?

    Requires Free Membership to View

IT Knowledge Exchange author Denny Cherry responds: There are a couple of ways to do this. If you have a newer version of SQL Server, you can do this with the SQL PIVOT statement. However, if you are using an older version of SQL Server, then the following code will do the trick to pivot this information for you just as a SQL PIVOT statement would. I've included code to create sample tables as well.

create table person (person_id int, name varchar(40), City varchar(40), Telephone varchar(10)) create table org (person_id int, org_id char(2))

insert into person

values

(1, 'Siva', 'Pondy', '1123')

 

insert into org

values

(1, 'O1'), (1, 'O2'), (1, 'O3')

 

select person.person_id, person.name, city, telephone,

                max(case when org.org_id = 'O1' then org_id end) 'O1',

                max(case when org.org_id = 'O2' then org_id end) 'O2',

                max(case when org.org_id = 'O3' then org_id end) 'O3',

                max(case when org.org_id = 'O4' then org_id end) 'O4'

from person

join org on person.person_id = org.person_id group by person.person_id, person.name, city, telephone

 

drop table person

drop table org

This was first published in December 2013

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: