Displaying data horizontally - 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

General SQL Server Forums

New to SQL Server Programming

Displaying data horizontally

Author

Topic

omega1983
Starting Member

40 Posts

Posted - 2008-02-07 : 10:26:59

In sql server, multiple instances of data default to a row display or vertical. I need a set of data in sql2005 to view horizontally so I can us it in a crystal report. Here is my issue.
gift.HonorKey, gift.HonorName, gift.HonorId
1211 Smith 1222
1244 Owens 4155

I need for the data to read like this:
HonorKey1, HonorKey2, HonorName1, HonorName2, HonorId1, Honorid2
1211 1244 Smith Owens 1222 4155

the table name is gift_view

I would like to be able to create a view in sql analyzer, then save as an SQL View
My direct email is jackfam@comcast.net

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-07 : 10:32:44

Good for you!

If we only care about the task in mind, you can use PIVOT or CROSSTAB algorithm to accomplish this.
In SQL Server 2005 there is a built-in PIVOT operator to use!

E 12°55'05.25"
N 56°04'39.16"

omega1983
Starting Member

40 Posts

Posted - 2008-02-07 : 10:58:20

Msg 325, Level 15, State 1, Line 12
Incorrect syntax near 'pivot'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

This is the error msg I get. I used the following code (modified for security)

select giftkey,giftid,gifthonor
from gifts_view
where gifthonor is not null
pivot

I am doing a simple VIEW here. Is there a workaround the error message I received???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-07 : 11:18:09

select giftkey,giftid,gifthonor
from gifts_view
where gifthonor is not null
pivot??

where's rest of code?
Did you refer to syntax of PIVOT in BOL?
You need to tell it what to pivot on.
Try it out yourself and let us know if you face any problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources