Transposing using pivot or XML

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 2005 Forums

Transact-SQL (2005)

Transposing using pivot or XML

Author

Topic

jayram
Starting Member

47 Posts

Posted - 2013-01-10 : 15:02:09

here is my table

CREATE TABLE [dbo].[CODE](
[CODE] [char](4) NOT NULL,
[1997] [float] NULL,
[1998] [float] NULL,
CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED
(
[CODE] ASC
)) ON [PRIMARY]

insert into CODE select '0001', 1, 2
insert into CODE select '0002', 4, 2
insert into CODE select '0003', 5, 6
insert into CODE select '0004', 8, 7
insert into CODE select '0005', 5, 2
insert into CODE select '0006', 3, 2

i want to transpose this into table or select query to be with 4 columns like this MSA, RATE, BEGIN (start of the column year), END (end of the column year)

insert into NEW select '0001', 1, '01/01/1997', '12/31/2007'
insert into NEW select '0002', 4, '01/01/1997', '12/31/2007'
insert into NEW select '0003', 5, '01/01/1997', '12/31/2007'
insert into NEW select '0004', 8, '01/01/1997', '12/31/2007'
insert into NEW select '0005', 5, '01/01/1997', '12/31/2007'
insert into NEW select '0006', 3, '01/01/1997', '12/31/2007'
insert into NEW select '0001', 2, '01/01/1998', '12/31/2008'
insert into NEW select '0002', 2, '01/01/1998', '12/31/2008'
insert into NEW select '0003', 6, '01/01/1998', '12/31/2008'
insert into NEW select '0004', 7, '01/01/1998', '12/31/2008'
insert into NEW select '0005', 2, '01/01/1998', '12/31/2008'
insert into NEW select '0006', 2, '01/01/1998', '12/31/2008'

How would i do this"

Thanks

jayram
Starting Member

47 Posts

Posted - 2013-01-10 : 15:37:43

i am using an unpivot function but it doesnt work

SELECT CODE, u.BEGIN, u.RATE FROM MSA_TEST UNPIVOT (RATE FOR BEGIN IN (WI_1997, WI_1998, WI_1999)) u

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 23:21:38

where are column MSA,RATE etc? i cant see them in current table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources