locked
Pivot Result set In SQL From Two Table RRS feed

  • 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

    Tuesday, November 3, 2020 9:44 AM

Answers

All replies

  • 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

    Wednesday, November 4, 2020 2:55 AM
  • 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

    Wednesday, November 4, 2020 7:32 PM
  • 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

    Thursday, November 5, 2020 9:44 AM
  • 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

    Thursday, November 5, 2020 10:50 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 6, 2020 6:26 AM