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?

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