Resim eklemedim fakat örnek bir data ve sorgu oluşturdum
Yapmak istediğim Pivot'a bağlı In içinde verilen ifadeleri bir Select sorgusu ile çekip göndermek
CREATE TABLE [dbo].[TestPivotAvans](
[IdAvans] [int] IDENTITY(1,1) NOT NULL,
[IdPersonel] [int] NULL CONSTRAINT [DF_TestPivotAvans_IdPersonel] DEFAULT ((0)),
[Tarih] [datetime] NULL CONSTRAINT [DF_TestPivotAvans_Tarih] DEFAULT (getdate()),
[Ay] [tinyint] NULL,
[Hafta] [tinyint] NULL,
[Tutar] [decimal](15, 4) NULL CONSTRAINT [DF_TestPivotAvans_Tutar] DEFAULT ((0)),
CONSTRAINT [PK_TestPivotAvans] PRIMARY KEY CLUSTERED
(
[IdAvans] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TestPivotPersonel](
[IdPersonel] [int] IDENTITY(1,1) NOT NULL,
[Ad] [nvarchar](50) NULL,
[Soyad] [nvarchar](50) NULL,
CONSTRAINT [PK_TestPivotPersonel] PRIMARY KEY CLUSTERED
(
[IdPersonel] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TestPivotAvans] ON
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (1, 1, CAST(N'2018-01-19 00:00:00.000' AS DateTime), 1, 3, CAST(5.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (2, 1, CAST(N'2018-02-01 00:00:00.000' AS DateTime), 2, 5, CAST(8.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (3, 1, CAST(N'2018-02-05 00:00:00.000' AS DateTime), 2, 6, CAST(5.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (4, 1, CAST(N'2018-03-14 00:00:00.000' AS DateTime), 3, 11, CAST(20.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (5, 1, CAST(N'2018-02-14 00:00:00.000' AS DateTime), 2, 7, CAST(40.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (6, 1, CAST(N'2018-03-06 00:00:00.000' AS DateTime), 3, 10, CAST(70.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (7, 1, CAST(N'2018-03-08 00:00:00.000' AS DateTime), 3, 10, CAST(90.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (8, 1, CAST(N'2018-03-05 00:00:00.000' AS DateTime), 3, 10, CAST(45.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (9, 2, CAST(N'2018-02-07 00:00:00.000' AS DateTime), 2, 6, CAST(78.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (10, 2, CAST(N'2018-03-14 00:00:00.000' AS DateTime), 3, 11, CAST(25.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (11, 2, CAST(N'2018-02-21 00:00:00.000' AS DateTime), 2, 8, CAST(65.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (12, 2, CAST(N'2018-01-09 00:00:00.000' AS DateTime), 1, 2, CAST(9.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (13, 2, CAST(N'2018-02-13 00:00:00.000' AS DateTime), 2, 7, CAST(42.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (14, 2, CAST(N'2018-02-19 00:00:00.000' AS DateTime), 2, 8, CAST(15.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (15, 3, CAST(N'2018-02-01 00:00:00.000' AS DateTime), 2, 5, CAST(73.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (16, 3, CAST(N'2018-03-05 00:00:00.000' AS DateTime), 3, 10, CAST(21.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (17, 3, CAST(N'2018-03-12 00:00:00.000' AS DateTime), 3, 11, CAST(85.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (18, 4, CAST(N'2018-02-06 00:00:00.000' AS DateTime), 2, 6, CAST(40.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (19, 4, CAST(N'2018-02-03 00:00:00.000' AS DateTime), 2, 5, CAST(68.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (20, 4, CAST(N'2018-03-05 00:00:00.000' AS DateTime), 3, 10, CAST(20.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (21, 4, CAST(N'2018-02-16 00:00:00.000' AS DateTime), 2, 7, CAST(17.0000 AS Decimal(15, 4)))
GO
INSERT [dbo].[TestPivotAvans] ([IdAvans], [IdPersonel], [Tarih], [Ay], [Hafta], [Tutar]) VALUES (22, 4, CAST(N'2018-03-17 00:00:00.000' AS DateTime), 3, 11, CAST(21.0000 AS Decimal(15, 4)))
GO
SET IDENTITY_INSERT [dbo].[TestPivotAvans] OFF
GO
SET IDENTITY_INSERT [dbo].[TestPivotPersonel] ON
GO
INSERT [dbo].[TestPivotPersonel] ([IdPersonel], [Ad], [Soyad]) VALUES (1, N'Ali', N'Demir')
GO
INSERT [dbo].[TestPivotPersonel] ([IdPersonel], [Ad], [Soyad]) VALUES (2, N'Veli', N'Tunç')
GO
INSERT [dbo].[TestPivotPersonel] ([IdPersonel], [Ad], [Soyad]) VALUES (3, N'Ayşe', N'Demir')
GO
INSERT [dbo].[TestPivotPersonel] ([IdPersonel], [Ad], [Soyad]) VALUES (4, N'Fatma', N'Tunç')
GO
SET IDENTITY_INSERT [dbo].[TestPivotPersonel] OFF
GO
Select Ad, Soyad, Ay, Hafta, Tutar
from TestPivotPersonel PR
left outer join TestPivotAvans AV on AV.IdPersonel = PR.IdPersonel
order by Hafta
go
Select *
from
(Select PR.Ad, PR.soyad, AV.Hafta, Tutar
from TestPivotPersonel PR
left outer join TestPivotAvans AV on AV.IdPersonel = PR.IdPersonel
) Tmp
PIVOT (sum(Tutar) for Hafta in ([2],[3],[5],[6],[7],[8],[10],[11]) ) as A
go