October 18, 2006 at 9:21 am
I never have understood loops. How would I get the data from Table1 into Table2? Thanks.
TableA:
Seq RowId DataSample
1 1 111111
2 2 A
3 3 Dog
4 4 Black
5 1 222222
6 2 B
7 3 Cat
8 4 Grey
9 1 333333
10 2 C
11 3 Bear
12 4 Brown
Table B:
AcctNo AlphaCode Animal Color
111111 A Dog Black
222222 B Cat Grey
333333 C Bear Brown
October 18, 2006 at 10:14 am
There is probably a better way:
-- Test data
DECLARE @t TABLE
(
Seq INT not null
,RowID INT not null
,DataSample VARCHAR(20) COLLATE DATABASE_DEFAULT NOT NULL
)
INSERT @t
SELECT 1, 1, '111111' UNION ALL
SELECT 2, 2, 'A' UNION ALL
SELECT 3, 3, 'Dog' UNION ALL
SELECT 4, 4, 'Black' UNION ALL
SELECT 5, 1, '222222' UNION ALL
SELECT 6, 2, 'B' UNION ALL
SELECT 7, 3, 'Cat' UNION ALL
SELECT 8, 4, 'Grey' UNION ALL
SELECT 9, 1, '333333' UNION ALL
SELECT 10, 2, 'C' UNION ALL
SELECT 11, 3, 'Bear' UNION ALL
SELECT 12, 4, 'Brown'
-- The query
SELECT D1.DataSample, D2.DataSample, D3.DataSample, D4.DataSample
FROM (
SELECT T1.DataSample
,T1.Seq/4 + 1 AS NewRow
FROM @t T1
WHERE T1.RowID = 1) D1
JOIN (
SELECT T2.DataSample
,T2.Seq/4 + 1 AS NewRow
FROM @t T2
WHERE T2.RowID = 2) D2
ON D1.NewRow = D2.NewRow
JOIN (
SELECT T3.DataSample
,T3.Seq/4 + 1 AS NewRow
FROM @t T3
WHERE T3.RowID = 3) D3
ON D1.NewRow = D3.NewRow
JOIN (
SELECT T4.DataSample
,T4.Seq/4 AS NewRow
FROM @t T4
WHERE T4.RowID = 4) D4
ON D1.NewRow = D4.NewRow
October 18, 2006 at 10:36 am
This is similar, but I have broken it down into an insert and separate updates. For me, this is easier to understand.
table TableA (Seq int, RowID int, DataSample varchar(20))
into TableA (Seq, RowID, DataSample)
1, 1,'111111'
2, 2, 'A'
3, 3, 'Dog'
4, 4,'Black'
5, 1, '222222'
6, 2, 'B'
7, 3, 'Cat'
8, 4, 'Grey'
9, 1,'333333'
10, 2,'C'
11, 3, 'Bear'
12, 4, 'Brown'
table tableB (AcctNo varchar(10), AlphaCode char(1) null,
(20) null, color varchar(20) null)
into TableB(AcctNo)
DataSample
TableA where RowID = 1
TableB
AlphaCode = C.AlphaCode
TableB join (
select a.DataSample as AcctNo, b.DataSample as AlphaCode
from TableA a join TableA b
on a.Seq + 1 = b.Seq
where a.RowID = 1) C
TableB.AcctNo = C.AcctNo
TableB
Animal = C.Animal
TableB join (
select a.DataSample as AcctNo, b.DataSample as Animal
from TableA a join TableA b
on a.Seq + 2 = b.Seq
where a.RowID = 1) C
TableB.AcctNo = C.AcctNo
TableB
Color = C.Color
TableB join (
select a.DataSample as AcctNo, b.DataSample as Color
from TableA a join TableA b
on a.Seq + 3 = b.Seq
where a.RowID = 1) C
TableB.AcctNo = C.AcctNo
* from TableB
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
October 18, 2006 at 10:40 am
Thanks guys. I'm going to put this on my laptop and take it with me to review this afternoon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply