Need Help with SQL Query

SM 1 Reputation point
2023-04-21T14:59:04.2466667+00:00

Hi Everyone, I need help with below output, could you please help me.

CREATE TABLE [Table1](
	[Prt] int NULL,
	[Sub1] int NULL,
	[Sub2] int NULL,
	[Sub3] int NULL) ON [PRIMARY]
GO

insert into [Table1] values
(182134,13453,54678,87654),
(182145,34553,34578,43254)

Output:

Part           sub

182134       13453
182134       54678
182134       87654
182145       34553
182145       34578
182145       43254

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,894 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Jingyang Li 5,891 Reputation points
    2023-04-21T16:05:23.7633333+00:00
    CREATE TABLE [Table1](
    	[Prt] int NULL,
    	[Sub1] int NULL,
    	[Sub2] int NULL,
    	[Sub3] int NULL) ON [PRIMARY]
    GO
    
    insert into [Table1] values
    (182134,13453,54678,87654),
    (182145,34553,34578,43254)
    
     
     select [Prt], Subs from [Table1]
     cross apply (values(Sub1),(Sub2),(Sub3))  unpvt(Subs);
     --or
    -- UNPIVOT  
    --   (Subs FOR vals IN   
    --      (Sub1,Sub2,Sub3)  
    --)AS unpvt;  
     
    
    
    
    DROP TABLE [Table1]
    
    0 comments No comments

  2. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-04-24T01:28:36.3966667+00:00

    Hi @SM

    In addition to the above method, you can also try this query.

    ;with CTE as(
      select Prt as Part,Sub1 as sub from table1
      union all
      select Prt,Sub2 from table1
      union all
      select Prt,Sub3 from table1)
    select * from CTE order by Part,sub;
    
    

    Output:

    User's image

    Best regards,

    Percy Tang


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments