insert columns of a table as rows of another table

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

SQL Server Development (2000)

insert columns of a table as rows of another table

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-17 : 04:58:13

Problem is, I have 3 columns in a table..>Table1

Col1 Col2 Col3 Name

1 5 7 A
4 8 10 B
10 2 21 C

I want to store in another table....>Table2 like

Name Mark

A 1
A 5
A 7
B 4
B 8
B 10
C 10
B 2
B 21

The number of rows in Table1 can vary.

How to do this

Thanks
Ceema

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-17 : 06:01:57

http://www.sqlteam.com/item.asp?ItemID=2652

Madhivanan

Failing to plan is Planning to fail

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-17 : 07:35:20

Hello,

Thank you so much

Ceema

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-17 : 07:58:29

Probably I'm missing something because I don't see the relevance of that link to the question. But then you seem happy with it, Ceema? I'm confused!

Well, anyway, here's an 'alternative'...

--data
declare @Table1 table (Col1 int, Col2 int, Col3 int, Name varchar(10))
insert @Table1
          select 1, 5, 7, 'A'
union all select 4, 8, 10, 'B'
union all select 10, 2, 21, 'C'

--calculation
declare @Table2 table (Name varchar(10), Mark int)
insert @Table2
          select Name, Col1 from @Table1
union all select Name, Col2 from @Table1
union all select Name, Col3 from @Table1

select * from @Table2 order by Name, Mark

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-17 : 08:02:33

How about somthing like this..


Select Name,Marks From 
(
Select Name, Col1 As Marks From Tbl1 
Union All 
Select Name, Col2 As Marks From Tbl1 
Union All 
Select Name, Col3 As Marks From Tbl1 
) As TblMarks

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-17 : 08:30:33

RyanRandall,

I was passing my thanks for the links you mailed, not for the entire solution. You spend some time for me, and that itself is a great thing in this world no?

Thank you
Ceema

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources