asked on

unpivot

Hi experts below is my original data
cust code1 code2 tex1 tex2

123 1 2 Y N

And I need to get results like this

cust code text
123 1 Y
123 2 N

But with my below query I am getting results as shown after query, please suggest

SELECT Distinct
Customer_No,
Code,
TextMessage
FROM (SELECT DISTINCT
Customer_no,
Code1,Code2, text1,text2)
FROM #final ) AS p UNPIVOT ( Code For
Col1 IN (Code1,Code2) ) AS unpvt1
UNPIVOT ( TextMessage For
Col2 IN (text1,text2) ) AS unpvt2
-- ) AS unpvt3
where Right(Col1,1)=Right(col2,1)

cust code text
123 1 Y
123 1 N

Scott Pletcher

PIVOT is so MS-centric (and somewhat convoluted), so in this case personally I'd just use CROSS APPLY:

SELECT
cust,
Code,
Text
FROM #final
CROSS APPLY (
SELECT Code1 AS Code, Text1 AS Text
UNION ALL
SELECT Code2, Text2
) AS ca
ORDER BY
cust, code

Anthony Perkins

But with my below query I am getting results as shown after query, please suggest
Any chance you can post the real query. Your results did not come from this query. It does not even compile.

Anthony Perkins

But I suspect this is what you are looking for:

SELECT  cust,
        code,
        [text]
FROM    (
	SELECT	cust,
               code1,
               code2,
               text1,
               text2
       FROM	final) f 
	UNPIVOT ( Code FOR Codes IN (code1, code2) ) u1 
	UNPIVOT ( [Text] FOR Texts IN (text1, text2) ) u2
WHERE   RIGHT(Codes, 1) = RIGHT(Texts, 1)

Open in new window

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION