Q

SQL PIVOT helps merge results into a single row

SQL Server expert Denny Cherry shows how code can be used to the same effect as a SQL PIVOT statement.

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

This was first published in December 2013

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close