Need Help with SQL Query

Question

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

Answer 1

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]

Answer 2

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:

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.