SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Transposing using pivot or XML
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jayram
Starting Member

44 Posts

Posted - 01/10/2013 :  15:02:09  Show Profile  Reply with Quote
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

44 Posts

Posted - 01/10/2013 :  15:37:43  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/10/2013 :  23:21:38  Show Profile  Reply with Quote
where are column MSA,RATE etc? i cant see them in current table

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000