Pivot Result set In SQL From Two Table

Question

User-807418713 posted

Hello

I Have Two Table : Table1 = Arrival & Table2 = Issues Below Is the sample demo data

USE [master]
GO
/****** Object:  Table [dbo].[Issues]    Script Date: 11/03/2020 15:11:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Issues](
	[Item] [varchar](50) NULL,
	[Issue_Date] [datetime] NULL,
	[Quantity] [float] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Issues] ([Item], [Issue_Date], [Quantity]) VALUES (N'A1', CAST(0x0000AC4A00000000 AS DateTime), 2)
INSERT [dbo].[Issues] ([Item], [Issue_Date], [Quantity]) VALUES (N'A1', CAST(0x0000AC4E00000000 AS DateTime), 6)
INSERT [dbo].[Issues] ([Item], [Issue_Date], [Quantity]) VALUES (N'A1', CAST(0x0000AC5700000000 AS DateTime), 4)
INSERT [dbo].[Issues] ([Item], [Issue_Date], [Quantity]) VALUES (N'B1', CAST(0x0000AC4F00000000 AS DateTime), 232)
INSERT [dbo].[Issues] ([Item], [Issue_Date], [Quantity]) VALUES (N'B1', CAST(0x0000AC7000000000 AS DateTime), 1)
INSERT [dbo].[Issues] ([Item], [Issue_Date], [Quantity]) VALUES (N'C1', CAST(0x0000AC7700000000 AS DateTime), 3)
INSERT [dbo].[Issues] ([Item], [Issue_Date], [Quantity]) VALUES (N'B1', CAST(0x0000AC8200000000 AS DateTime), 5)
/****** Object:  Table [dbo].[Arrival]    Script Date: 11/03/2020 15:11:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Arrival](
	[Item] [varchar](50) NULL,
	[Arrival_Date] [datetime] NULL,
	[Quantity] [float] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Arrival] ([Item], [Arrival_Date], [Quantity]) VALUES (N'A1', CAST(0x0000AC4700000000 AS DateTime), 10)
INSERT [dbo].[Arrival] ([Item], [Arrival_Date], [Quantity]) VALUES (N'A1', CAST(0x0000AC4900000000 AS DateTime), 5)
INSERT [dbo].[Arrival] ([Item], [Arrival_Date], [Quantity]) VALUES (N'A1', CAST(0x0000AC5500000000 AS DateTime), 20)
INSERT [dbo].[Arrival] ([Item], [Arrival_Date], [Quantity]) VALUES (N'B1', CAST(0x0000AC4B00000000 AS DateTime), 10)
INSERT [dbo].[Arrival] ([Item], [Arrival_Date], [Quantity]) VALUES (N'B1', CAST(0x0000AC6B00000000 AS DateTime), 4)
INSERT [dbo].[Arrival] ([Item], [Arrival_Date], [Quantity]) VALUES (N'C1', CAST(0x0000AC7000000000 AS DateTime), 20)
INSERT [dbo].[Arrival] ([Item], [Arrival_Date], [Quantity]) VALUES (N'B1', CAST(0x0000AC8000000000 AS DateTime), 10)
INSERT [dbo].[Arrival] ([Item], [Arrival_Date], [Quantity]) VALUES (N'A1', CAST(0x0000AC6600000000 AS DateTime), 10)

I want output like this below format

Item_Name	Arrival OCT-2020	Issues OCT-2020	EXCESS / SHORTAGE OF OCT -2020	Arrival NOV-2020	Issues NOV-2020	EXCESS / SHORTAGE OF NOV -2020
A1	35	12	23	10		10
B1	10	2	8	14	6	8
C1				20	3	17
TOTAL	45	14		44	9

Thanking You

Answer 1

User1535942433 posted

Hi Gopi.MCA,

Accroding to your description,I don't understand your requirment.

What's Arrival OCT-2020,Issues OCT-2020,EXCESS / SHORTAGE OF OCT -2020,Arrival NOV-2020,Issues NOV-2020,Issues NOV-2020?

How to get these results?

Could you post more details to us?It will help us to solve your problems.

Best regards,

Yijing Sun

Answer 2

User-807418713 posted

Hello

What's Arrival OCT-2020,Issues OCT-2020,EXCESS / SHORTAGE OF OCT -2020,Arrival NOV-2020,Issues NOV-2020,Issues NOV-2020?

here Arrival OCT-2020 Is Total Quantity Of Item Arrival ON October Month 2020 Is = 35

Issues OCT-2020 Is Total Quantity Of Item Issues ON October Month 2020 Is = 12

Excess / Shortage Of October Month = 23

Hope now its clear..

Thanking You

Answer 3

User1535942433 posted

Hi Gopi.MCA,

You could use like this:

select t1.Item_Name,t2.Arrival_OCT_2020,t3.Issues_OCT_2020,t2.Arrival_OCT_2020-t3.Issues_OCT_2020 as EXCESS_SHORTAGE_OCT_2020,t4.Arrival_NOV_2020,t5.Issues_NOV_2020,t4.Arrival_NOV_2020-t5.Issues_NOV_2020 as EXCESS_SHORTAGE_NOV_2020
from 
(select distinct Item as Item_Name from Arrival) t1
left join
(select Item as Item_Name,SUM(Quantity) as Arrival_OCT_2020 from Arrival where MONTH(Arrival_Date)=10  group by Item) t2
on t1.Item_Name=t2.Item_Name
left join
(select Item as Item_Name,SUM(Quantity) as Issues_OCT_2020 from Issues where MONTH(Issue_Date)=10  group by Item) t3
on t1.Item_Name=t3.Item_Name
left join
(select Item as Item_Name,SUM(Quantity) as Arrival_NOV_2020 from Arrival where MONTH(Arrival_Date)=11  group by Item) t4
on t1.Item_Name=t4.Item_Name
left join
(select Item as Item_Name,SUM(Quantity) as Issues_NOV_2020 from Issues where MONTH(Issue_Date)=11  group by Item) t5
on t1.Item_Name=t5.Item_Name

Union all
select 'Total',SUM(t2.Arrival_OCT_2020) as Arrival_OCT_2020,SUM(t3.Issues_OCT_2020) as Issues_OCT_2020,'',SUM(t4.Arrival_NOV_2020) as Arrival_NOV_2020,SUM(t5.Issues_NOV_2020) as Issues_NOV_2020,''
from 
(select distinct Item as Item_Name from Arrival) t1
left join
(select Item as Item_Name,SUM(Quantity) as Arrival_OCT_2020 from Arrival where MONTH(Arrival_Date)=10  group by Item) t2
on(t1.Item_Name=t2.Item_Name)
left join
(select Item as Item_Name,SUM(Quantity) as Issues_OCT_2020 from Issues where MONTH(Issue_Date)=10  group by Item) t3
on (t1.Item_Name=t3.Item_Name)
left join
(select Item as Item_Name,SUM(Quantity) as Arrival_NOV_2020 from Arrival where MONTH(Arrival_Date)=11  group by Item) t4
on t1.Item_Name=t4.Item_Name
left join
(select Item as Item_Name,SUM(Quantity) as Issues_NOV_2020 from Issues where MONTH(Issue_Date)=11  group by Item) t5
on t1.Item_Name=t5.Item_Name

Result:

Best regards,

Yijing Sun

Answer 4

User-807418713 posted

hello

I need this in dynamic becuase our table will have data of any month for demo i have given october, novemeber month sample..

thanking you

Answer 5

User1535942433 posted

Hi Gopi.MCA,

As far as I think,you could use dynamical pivot. First,you scan the month column and then you could select the result using stored procedure.

Just like this:

https://stackoverflow.com/questions/12643117/dynamically-create-columns-sql

Best regards,

Yijing Sun