CODEWITHSUNDEEP
sqlsql-server
user1630575
user1630575

Reputation: 171

Query to move value from Columns to rows

I have two tables and I want to merge the data in third table as shown below,

Table 1:

User Id  Account1  Account 2  
-------  --------  ---------
Lucky    Twitter   Facebook  
Happy    Orkut     GooglePlus

Table 2 

User Id  AccountStatus 
-------  ------------- 
Lucky    Active

I want the result as:

UserId   Account  
------   --------
Lucky    Twitter  
Lucky    Facebook

Any idea on how to do it ?

Upvotes: 1

Views: 89

Answers (3)

bhushanvinay
bhushanvinay

Reputation: 449

; With cteActiveAccount As(
   Select UserId
   From Table2 where AccountStatus = 'Active'
 )
, cteAccountFilterdOnes As(
  Select r.UserId
         r.Account1 as Account 
  from Table1 r
  inner join cteActiveAccount c on
      r.UserId = c.UserId
  union all
  Select r.UserId
         r.Account2 as Account 
  from Table1 r
  inner join cteActiveAccount c on
      r.UserId = c.UserId
  )

select * from cteAccountFilterdOnes

Upvotes: 1

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

SQL Server has native support for UNPIVOT, so you should use it:

SELECT UserI
FROM
(
    SELECT A.UserId, Account1, Account2
    FROM Account A
        JOIN AccountStatus S ON S.UserId = A.UserId
    WHERE S.AccountStatus = 'Active'
) AS acc
UNPIVOT 
(
   Account FOR Acc IN (Account1, Account2)
) AS up;

I don't know if it is faster, but it allows you to easily extend your account types just by adding columns in two places (SELECT and within UNPIVOT).

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

Just unpivot the data to get the result in format

using CROSS APPLY operator

SELECT [user id], 
       account 
FROM   table1 A 
       CROSS apply (VALUES (account1), 
                           (account2)) cs(account) 
WHERE  EXISTS (SELECT 1 
               FROM   table2 B 
               WHERE  A.[user id] = B.[user id]
                 AND  B.AccountStatus = 'Active')

or use UNION ALL to unpivot the data

SELECT * 
FROM   (SELECT [user id], 
               account1  as account
        FROM   table1 
        UNION ALL 
        SELECT [user id], 
               account2 
        FROM   table1) A 
WHERE  EXISTS (SELECT 1 
               FROM   table2 B 
               WHERE  A.[user id] = B.[user id]
                 AND  B.AccountStatus = 'Active')

Upvotes: 2

Related Questions