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
(1, 'Siva', 'Pondy', '1123')
insert into org
(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'
join org on person.person_id = org.person_id group by person.person_id, person.name, city, telephone
drop table person
drop table org
Dig Deeper on SQL-Transact SQL (T-SQL)
Related Q&A from Mark Fontecchio
Database expert Michael Abbey explains what's needed to ensure that your Oracle physical standby databases are vetted and work properly. Continue Reading