May 17, 2006 at 8:21 pm
Hai all,
I have a table that look like this :
ID MATH BIO CHEM ENG
1 8 8 8 8
2 7 7 7 7
I want to convert into :
SUBJECT VALUE ID
MATH 8 1
BIO 8 1
CHEM 8 1
ENG 8 1
MATH 7 2
BIO 7 2
CHEM 7 2
ENG 7 2
If anyone knows how to do it, please help...
Thanks.
-thuthu-
May 18, 2006 at 5:49 am
If this is a one time event, and the tables data is relatively small, you could copy and paste the data into Excel. In Excel, copy the data again and in a new page, right click – paste special – transpose. Save as .csv and import into the database as a new table. Else you could use single queries for each row, that select the same values, and stack them with "Union All"s to form the new layout. If you are looking for Elegance, search thise site for Transpose Data.
MISfIT
May 18, 2006 at 6:26 am
Not help exactly - more like education in etiquette 🙂
Rule # 1: Thou shalt not cross-post!
May 18, 2006 at 8:37 am
--DROP TABLE original
GO
CREATE TABLE original
(
rid int
, math int
, bio int
, chem int
, eng int
)
GO
SET NOCOUNT ON
INSERT original VALUES (1, 8, 8, 8, 8)
INSERT original VALUES (2, 7, 7, 7, 7)
SET NOCOUNT OFF
GO
--DROP TABLE newTable
GO
CREATE TABLE newTable
(
subject varchar(20)
, value int
, rid int
)
GO
SET NOCOUNT ON
INSERT newTable SELECT 'MATH', math, rid FROM original
INSERT newTable SELECT 'BIO', bio, rid FROM original
INSERT newTable SELECT 'CHEM', chem, rid FROM original
INSERT newTable SELECT 'ENG', eng, rid FROM original
SET NOCOUNT OFF
SELECT subject, value, rid
FROM newTable
ORDER BY rid, subject
June 24, 2006 at 7:11 am
Hi !
What can i do if the columns have a dynamic name and variuos number from time to time ?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply