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
Installing SAP HANA is no easy task. Read about why Scotts Miracle-Gro decided on a phased rollout of its HANA implementation. Continue Reading
Escaping the no-win Oracle SLA as a DBA is all about tying technology to business, according to two Oracle Database experts. Continue Reading
PayPal Database Engineer John Kanagaraj says an AWR workaround lets you prioritize and compress data in earlier ODB versions. Continue Reading