Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 148
  • Last Modified:

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
0
sqlcurious
Asked:
sqlcurious
2 Solutions
 
Scott PletcherSenior DBACommented:
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
0
 
Anthony PerkinsCommented:
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.
0
 
Anthony PerkinsCommented:
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

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Anthony PerkinsCommented:
This is how I tested it:
DECLARE @final TABLE(cust integer, code1 integer, code2 integer, text1 char(1), text2 char(1))
INSERT @final (cust, code1, code2, text1, text2)
VALUES  (123, 1, 2, 'Y', 'N')

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

And here is the output:
cust	code	text
123	1	Y
123	2	N

Open in new window

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

PIVOT &/or UNPIVOT are used by more than one dbms vendor but I know of only MS who uses "apply" cross or outer; so I can't think of a more MS centric approach than cross apply

However I agree, I much prefer the directness of using CROSS APPLY for unpivots.

A small variation is to use VALUES where you can literally "see" the layout
SELECT
      cust
    , ca.Code
    , ca.Text
FROM #final
      CROSS APPLY (
            VALUES
                    (Code1, Text1)
                  , (Code2, Text2)
                  ) AS ca (Code, Text)
ORDER BY
      cust, code

Open in new window

0
 
sqlcuriousAuthor Commented:
thanks
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now