CODEWITHSUNDEEP
sql-servert-sqlunpivot
michal
michal

Reputation: 11

MS SQL copy data from another table by rows

I have following 2 tables:

enter image description here

DECLARE @table1 TABLE (
    [NO] int,
    A1 float, 
    A2 float, 
    A3 float, 
    A4 float, 
    A5 float,
    A6 float
)

DECLARE @table2 TABLE (
    KOD nvarchar(100), 
    ID_QUANTITY int
)

INSERT INTO @table1 VALUES
(1, 4.1, 3, 3.5, 23.5, 12.2, 2.4),
(2, 4.2, 0, 1.9, 34.5, 31.2, 34.2),
(3, 1.5, 0, 2.3, 12.3, 12.3, 1.2),
(4, 3.7, 2.1, 5.9, 4.8, 10.2, 21.2),
(5, 3.9, 2.6, 1.9, 12.3, 2.4, 10.2)

INSERT INTO @table2 VALUES
('A1', 500),
('A2', 600),
('A3', 700),
('A4', 800),
('A5', 900),
('A6', 1000)

And I need create script in sql for transform value in first table to appearance as below

enter image description here

Thank you for your help

Upvotes: 1

Views: 62

Answers (3)

mvisser
mvisser

Reputation: 670

Use UNPIVOT 

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
    t2.ID_Quantity,
    t1.Value 
FROM Table2 t2
INNER JOIN 
(
            SELECT * 
            FROM Table1
            UNPIVOT 
            (
                [Values] FOR [Columns] IN ([A1], [A2],[A3], [A4], [A5],[A6])
            ) as pvt
) t1
ON t1.Columns = t2.KOD

Upvotes: 1

J. Zend
J. Zend

Reputation: 98

try this :

create table3(ID_QUANTITY int(11),VALUE int(11));

for($i=1;$i<6;$i++){ $insert=mysql_query("insert into table3 select a.ID_QUANTITY,b.A$i from table2 a,table1 b where a.KOD='A$i'); }

Upvotes: 0

gofr1
gofr1

Reputation: 15987

UNPIVOT then JOIN, use ROW_NUMBER() for ID column:

SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as ID,
        ID_QUANTITY,
        [VALUES]
FROM (
    SELECT *
    FROM table1
    ) p
UNPIVOT (
    [VALUES] FOR [COL] IN (A1,A2,A3,A4,A5,A6)
) as unpvt
LEFT JOIN table2 t 
    ON t.KOD = unpvt.[COL]
ORDER BY ID_QUANTITY, [NO]

Output:

ID  ID_QUANTITY VALUES
1   500         4,1
2   500         4,2
3   500         1,5
4   500         3,7
5   500         3,9
6   600         3
7   600         0
8   600         0
9   600         2,1
10  600         2,6
...
25  900         2,4
26  1000        2,4
27  1000        34,2
28  1000        1,2
29  1000        21,2
30  1000        10,2

Upvotes: 1

Related Questions