question

thennarasu avatar image
thennarasu asked

Insert multiple column from one table into two coloums in another table

i have a tablename called emp
EmpID	Amount	Empid1	Amount1 	Empid2	Amount2	Empid3	Amount3
  1	    2000	   2	  2000	       3	2000	   4	2000
  1	    1500	   2	  1500	       3	1500	   4	3500
  1	    1000	   2	  1000	       3	1000	   4	1000
need to insert this table into another table named salary, which has only two columns insert multiple rows into two rows result shoul be like this

      EmpID	     Amount
        1	     2000
        1	     1500
        1	     1000
        2	     2000
        2	     1500
        2	     1000
       3	     1000
       3	     1500
       3	     2000
       4	     1000
       4	     3500
       4	     2000

sql-server-2008sql-server-2005
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Usman Butt avatar image
Usman Butt answered
This is a very bad design to work with and seems like there would be more columns than you have specified. But for this specific problem the following code should work declare @Emp table ( EmpID int, Amount int, Empid1 int, Amount1 int, Empid2 int, Amount2 int, Empid3 int, Amount3 int ) insert @Emp select 1, 2000, 2, 2000, 3, 2000, 4, 2000 union all select 1, 1500, 2, 1500, 3, 1500, 4, 3500 union all select 1, 1000, 2, 1000, 3, 1000, 4, 1000 --SQL 2k8+ select x.* from @Emp cross apply (values(EmpID, Amount), (EmpID1, Amount1), (EmpID2, Amount2), (EmpID3, Amount3))x(Emp, Amount) order by Emp --SQL 2k5 select x.* from @Emp cross apply (select EmpID, Amount union all select EmpID1, Amount1 union all select EmpID2, Amount2 union all select EmpID3, Amount3 )x(Emp, Amount) order by Emp
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

when i was writing answer i wasn't see this pictures, this was some technical issue, Yes Usman you are right, it is kind of pivot or unpivot
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.