| Author |
 Topic  |
|
|
jayram
Starting Member
27 Posts |
 Posted - 01/10/2013 : 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
27 Posts |
Posted - 01/10/2013 : 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
India
47157 Posts |
Posted - 01/10/2013 : 23:21:38
|
where are column MSA,RATE etc? i cant see them in current table
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Transposing using pivot or XML
