converting columns to row - 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)

converting columns to row

Author

Topic

rabisco
Starting Member

15 Posts

Posted - 2008-08-26 : 14:26:03

I am hoping someone can hello. I. hoping some here can help be - I'm getting really desperate.

I have the following view....


CREATE VIEW dbo.vFundPriceHistory
AS
SELECT     TOP 100 PERCENT CONVERT(varchar(16), fph.PriceDate, 3) AS [Date], f.Name + '-' + LEFT(c.Name, 2) AS CurrencyName, fph.Price
FROM         dbo.FundPriceHistory fph LEFT OUTER JOIN
                      dbo.Fund f ON fph.FundId = f.FundId LEFT OUTER JOIN
                      dbo.Currency c ON fph.CurrencyId = c.CurrencyId
ORDER BY fph.[Date]

Out of which I can do a select * and be returned

Date, CurrencyName, Price.

The user want to the data in the following format..

CurrencyName CurrencyName CurrencyName
Date Price Price Price
Date Price Price Price
Date Price Price

This is on sql server 2000

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-26 : 16:36:33

you are wanting to pivot the normalised table.

Do a search on the 2000 formus for 'pivot' and you will come up with lots and lots of examples of how to do this... Most people post their topic something like : "row to column help"

The best way is to do this in the front end application. If you absolutely have to do this on the database side for an unknown number / collection of names then you are going to have to get into some dynamic sql.

-------------
Charlie