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

Question

question

thennarasu asked Mar 26, '18

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-2008 sql-server-2005

10 |1200

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

Answer 1 · 2018-03-26T13:45:55Z

Usman Butt answered Mar 26, '18

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

10 |1200

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

Lukasz8519 commented · Mar 26, 2018 at 01:54 PM

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 ·

question