Transform Table - 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)

Transform Table

Author

Topic

mufasa
Yak Posting Veteran

62 Posts

Posted - 2004-08-05 : 14:57:05

Hi
I am trying to transform columns into rows
example:
Existing table
c1=Date, c2=Store, c3=VisaAmount, c4=MCAmount, c5=DebitAmount.

View Wanted
I need to create a view with the following
c1=Date, c2=Store, c3=Cardtype, c4=Amount

Thanks

Mufasa

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-05 : 15:16:46

[code]
USE Northwind
GO

CREATE TABLE myTable99(
[Date] datetime
, Store varchar(25)
, VisaAmount money
, MCAmount money
, DebitAmount money
)
GO

INSERT INTO myTable99([Date], Store, VisaAmount, MCAmount, DebitAmount)
SELECT '1/1/2004','Home Depot',12424.1234,2656.24,9345
GO

CREATE VIEW myView99
AS
SELECT [Date], Store, 'Visa' AS CardType, VisaAmount AS Amount
FROM myTable99
UNION ALL
SELECT [Date], Store, 'MC' AS CardType, MCAmount AS Amount
FROM myTable99
UNION ALL
SELECT [Date], Store, 'Debit' AS CardType, DebitAmount AS Amount
FROM myTable99
GO

SELECT * FROM myView99

DROP VIEW myView99
DROP TABLE myTable99
GO

[/code]

Brett

8-)

mufasa
Yak Posting Veteran

62 Posts

Posted - 2004-08-05 : 15:54:07

Thanks Brett

Works great

Mufasa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources