Hi,
I'm wanting to manipulate data between two tables in the following manner.
Table1
Column1 Derived Column ValueX ValueY
1 0 100 100
2 0 40 60
3 0 30 70
4 0 90 85
5 0 10 102
Table 2
IDColumn Qty1 Qty2 Qty3 Qty4 Qty5
a 10 20 30 40 50
I need to take the data in columns Qty1 - Qty5 in Table2 and transpose it into the Derived Column in Table 1.
Leaving the ValueX and ValueY columns as they are relative to column1.
I believe this can be done using a Pivot Table, but cannot find enough clear information from which to learn how to do it.
Any help will be welcomed.
John
hi,
your metadata misses the (eventual) relation between the 2 tables, as you obviously only reported a partial schema...
please consider posting a condesed schema with all required elements in a CREATE TABLE ( col1 datatype, colx datatype) format in order not to make us guessing all the metaschema.. 
considering 2 relations (tables) like
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.t1 (
Id int NOT NULL,
t2Ref char(1) NOT NULL,
derCol int NULL,
ValueX int NOT NULL,
ValueY int NOT NULL,
CONSTRAINT pk_t1 PRIMARY KEY (t2Ref, id)
);
CREATE TABLE dbo.t2 (
Id char(1) NOT NULL PRIMARY KEY,
Qty1 int NOT NULL,
Qty2 int NOT NULL,
Qty3 int NOT NULL,
Qty4 int NOT NULL,
Qty5 int NOT NULL
);
GO
INSERT INTO dbo.t1 VALUES ( 1 ,'a' , NULL, 100, 100 );
INSERT INTO dbo.t1 VALUES ( 2 ,'a', NULL, 40, 60 );
INSERT INTO dbo.t1 VALUES ( 3 ,'a', NULL, 30, 70 );
INSERT INTO dbo.t1 VALUES ( 4 ,'a', NULL, 90, 85 );
INSERT INTO dbo.t1 VALUES ( 5 ,'a', NULL, 10, 102 );
INSERT INTO dbo.t1 VALUES ( 1 ,'b' , NULL, 100, 100 );
INSERT INTO dbo.t1 VALUES ( 2 ,'b', NULL, 40, 60 );
INSERT INTO dbo.t1 VALUES ( 3 ,'b', NULL, 30, 70 );
INSERT INTO dbo.t1 VALUES ( 4 ,'b', NULL, 90, 85 );
INSERT INTO dbo.t1 VALUES ( 5 ,'b', NULL, 10, 102 );
INSERT INTO dbo.t2 VALUES ( 'a' , 10, 20, 30, 40, 50 );
INSERT INTO dbo.t2 VALUES ( 'b' , 100, 200, 300, 400, 500 );
which actually implies, for your unpivot to work that dbo.t1 always have 5 rows, from Id=1 to id=5 for each dbo.t1.t2Ref as the insert statements provides, you can rotate the the dbo.t2 table via the UNPIVOT statement, as you already pointed out, like
SELECT Id AS IdRef, CONVERT(int,RIGHT(IdRef,1)) AS Id, QT
FROM
(SELECT Id, Qty1, Qty2, Qty3, Qty4, Qty5
FROM dbo.t2) p
UNPIVOT
(QT FOR IdRef IN
(Qty1, Qty2, Qty3, Qty4, Qty5)
)AS unpvt
GO
--<--
IdRef Id QT
-- -- --
a 1 10
a 2 20
a 3 30
a 4 40
a 5 50
b 1 100
b 2 200
b 3 300
b 4 400
b 5 500
and you get an idea of the rotated output... you can then both project the joined result of the base dbo.t1 table with the work table resulting from the UNPIVOTing as
PRINT 'SELECT JOINED projection';
SELECT t1.Id, t1.t2Ref, r.IdRef, r.QT, t1.ValueX, t1.ValueY
FROM dbo.t1 t1
JOIN
(SELECT Id AS IdRef, CONVERT(int,RIGHT(IdRef,1)) AS Id, QT
FROM
(SELECT Id, Qty1, Qty2, Qty3, Qty4, Qty5
FROM dbo.t2) p
UNPIVOT
(QT FOR IdRef IN
(Qty1, Qty2, Qty3, Qty4, Qty5)
)AS unpvt ) AS r
ON r.IdRef= t1.t2Ref AND r.Id = t1.Id
ORDER BY t1.t2Ref, t1.Id;
--<
SELECT JOINED projection
Id t2Ref IdRef QT ValueX ValueY
-- -- -- -- -- --
1 a a 10 100 100
2 a a 20 40 60
3 a a 30 30 70
4 a a 40 90 85
5 a a 50 10 102
1 b b 100 100 100
2 b b 200 40 60
3 b b 300 30 70
4 b b 400 90 85
5 b b 500 10 102
and obviously code a JOINed UPDATE statement that updates the [derCol] from the projection of the upper SELECT as
PRINT 'UPDATEd JOIN';
UPDATE t1
SET derCol = r.QT
FROM dbo.t1 t1
JOIN
(SELECT Id AS IdRef, CONVERT(int,RIGHT(IdRef,1)) AS Id, QT
FROM
(SELECT Id, Qty1, Qty2, Qty3, Qty4, Qty5
FROM dbo.t2) p
UNPIVOT
(QT FOR IdRef IN
(Qty1, Qty2, Qty3, Qty4, Qty5)
)AS unpvt ) AS r
ON r.IdRef= t1.t2Ref AND r.Id = t1.Id;
the final output will be
SELECT * FROM dbo.t1;
--<-
Id t2Ref derCol ValueX ValueY
-- -- -- -- --
1 a 10 100 100
2 a 20 40 60
3 a 30 30 70
4 a 40 90 85
5 a 50 10 102
1 b 100 100 100
2 b 200 40 60
3 b 300 30 70
4 b 400 90 85
5 b 500 10 102
that should corespond to your requirements...
[COMPLETE SCRIPT]
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.t1 (
Id int NOT NULL,
t2Ref char(1) NOT NULL,
derCol int NULL,
ValueX int NOT NULL,
ValueY int NOT NULL,
CONSTRAINT pk_t1 PRIMARY KEY (t2Ref, id)
);
CREATE TABLE dbo.t2 (
Id char(1) NOT NULL PRIMARY KEY,
Qty1 int NOT NULL,
Qty2 int NOT NULL,
Qty3 int NOT NULL,
Qty4 int NOT NULL,
Qty5 int NOT NULL
);
GO
INSERT INTO dbo.t1 VALUES ( 1 ,'a' , NULL, 100, 100 );
INSERT INTO dbo.t1 VALUES ( 2 ,'a', NULL, 40, 60 );
INSERT INTO dbo.t1 VALUES ( 3 ,'a', NULL, 30, 70 );
INSERT INTO dbo.t1 VALUES ( 4 ,'a', NULL, 90, 85 );
INSERT INTO dbo.t1 VALUES ( 5 ,'a', NULL, 10, 102 );
INSERT INTO dbo.t1 VALUES ( 1 ,'b' , NULL, 100, 100 );
INSERT INTO dbo.t1 VALUES ( 2 ,'b', NULL, 40, 60 );
INSERT INTO dbo.t1 VALUES ( 3 ,'b', NULL, 30, 70 );
INSERT INTO dbo.t1 VALUES ( 4 ,'b', NULL, 90, 85 );
INSERT INTO dbo.t1 VALUES ( 5 ,'b', NULL, 10, 102 );
INSERT INTO dbo.t2 VALUES ( 'a' , 10, 20, 30, 40, 50 );
INSERT INTO dbo.t2 VALUES ( 'b' , 100, 200, 300, 400, 500 );
SELECT Id AS IdRef, CONVERT(int,RIGHT(IdRef,1)) AS Id, QT
FROM
(SELECT Id, Qty1, Qty2, Qty3, Qty4, Qty5
FROM dbo.t2) p
UNPIVOT
(QT FOR IdRef IN
(Qty1, Qty2, Qty3, Qty4, Qty5)
)AS unpvt
GO
GO
PRINT 'SELECT JOINED projection';
SELECT t1.Id, t1.t2Ref, r.IdRef, r.QT, t1.ValueX, t1.ValueY
FROM dbo.t1 t1
JOIN
(SELECT Id AS IdRef, CONVERT(int,RIGHT(IdRef,1)) AS Id, QT
FROM
(SELECT Id, Qty1, Qty2, Qty3, Qty4, Qty5
FROM dbo.t2) p
UNPIVOT
(QT FOR IdRef IN
(Qty1, Qty2, Qty3, Qty4, Qty5)
)AS unpvt ) AS r
ON r.IdRef= t1.t2Ref AND r.Id = t1.Id
ORDER BY t1.t2Ref, t1.Id;
PRINT 'UPDATEd JOIN';
UPDATE t1
SET derCol = r.QT
FROM dbo.t1 t1
JOIN
(SELECT Id AS IdRef, CONVERT(int,RIGHT(IdRef,1)) AS Id, QT
FROM
(SELECT Id, Qty1, Qty2, Qty3, Qty4, Qty5
FROM dbo.t2) p
UNPIVOT
(QT FOR IdRef IN
(Qty1, Qty2, Qty3, Qty4, Qty5)
)AS unpvt ) AS r
ON r.IdRef= t1.t2Ref AND r.Id = t1.Id;
SELECT * FROM dbo.t1;
GO
DROP TABLE dbo.t1, dbo.t2; -- clean up
[/COMPLETE SCRIPT] again, I assumed the existance of a dbo.t1.d2Ref column related to t2.Id column, without that all the scenario fades out.. regards|||
Thank you so much for your detailed answer, and my apologies for not providing enough information.
As a novice it's difficult to get an understanding of some of the basic things. eg. I was not sure that PIVOT always turns column data into a row, and UNPIVOT, does the opposite. I guessed that was the way it happened, but the examples on MSDN are certainly not clear enough for a beginner.
The example you have provided clarifies the situation for me, and should enable me to proceed.
Again, my sincere thanks for taking the time to answer.
John
as [Label a], pt.
as [Label b]...
....)) as pt