UNPIVOT Result set - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

UNPIVOT Result set

Author

Topic

ramdas
Posting Yak Master

181 Posts

Posted - 2005-07-21 : 10:39:38

Hi Folks,
I have created a table with the following DDL:
CREATE TABLE tbl_ffmethod
([id] char(10) null,
Internet char(1) null,
Telephone char(1) null,
Travel_agency char(1) null
)

The current result set of the table is
ID Internet Telephone Travel Agency
ABCD N Y Y
EFGH Y Y N
XYZH N N N
F212 Y N N

I want the results to be in the form

ABCD Telephone
ABCD Travel Agency
EFGH Internet
EFGH Telephone
EFGH Travel Agency

Any suggestions, Thank you

Ramdas Narayanan
SQL Server DBA

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-21 : 11:05:29

Why have you created an unnormalized table in the first place? Does this table have a primary key? Why are all those columns nullable?

Are you really a DBA?

A UNION ALL with 3 "passes" through the table, one per column, is probably the easiest way to go. Or, if you have a table listing the 3 types (1 value per row), you can cross join with that table and use a CASE to pull the value from the proper column.

- Jeff

ramdas
Posting Yak Master

181 Posts

Posted - 2005-07-21 : 11:33:02

Hi,
Thank you for the solution.
I am working on a sample data set to try the unpivot of data.

Ramdas

Ramdas Narayanan
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources