quote:

---

Originally posted by Looper

Thanks - I am new to this and am a little confused by unpivot, could you show me an example for my scenerio?

---

--sample data preparation dont worry about this
DECLARE @Test table
(
PayNo int,
PayCode varchar(10),
Code1 varchar(10),
Amount1 int,
Code2 varchar(10),
Amount2 int
)


INSERT INTO @Test
VALUES (101,'A001','UGKI',100,'kjh',123),
(102,'A002','UGKI',124,'sadg',345),
(104,'A004','few',678,'savd',563),
(105,'A005','sadv',658,'sav',142),
(106,'A006','awv',547,'wer',547),
(109,'A009','svvs',326,'wrwre',457)


--actual solution
SELECT a.PayNo,a.PayCode,a.value,b.val1
FROM
(
select PayNo,PayCode,value,
ROW_NUMBER() OVER (PARTITION BY PayNo,PayCode ORDER BY PayNo) AS Seq
from @Test
unpivot(value for cat in (Code1,Code2))u
)a
JOIN
(

select PayNo,PayCode,val1,
ROW_NUMBER() OVER (PARTITION BY PayNo,PayCode ORDER BY PayNo) AS Seq
from @Test
unpivot(val1 for cat1 in (Amount1,Amount2))v
)b
ON a.PayNo=b.PayNo
AND a.PayCode=b.PayCode
AND a.Seq=b.Seq

output
----------------
101	A001	UGKI	100
101	A001	kjh	123
102	A002	UGKI	124
102	A002	sadg	345
104	A004	few	678
104	A004	savd	563
105	A005	sadv	658
105	A005	sav	142
106	A006	awv	547
106	A006	wer	547
109	A009	svvs	326
109	A009	wrwre	457