Separate records to column year wise in SQL Server

PRA
 
on Feb 10, 2021 10:24 PM
478 Views

Hi!

How to separate fees and learnyear by years

I used below script. 

DECLARE @Students AS TABLE(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nchar](20) NULL
)
INSERT INTO @Students ([Name]) VALUES (N'Rustam')
INSERT INTO @Students ([Name]) VALUES (N'Muhammad')
INSERT INTO @Students ([Name]) VALUES (N'Amirjon')
INSERT INTO @Students ([Name]) VALUES (N'Sadriddin')
--select * from @Students

DECLARE @Cash AS TABLE(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    IdStd int,
    Course int,
    Fees money,
    LearnYear char(7)
)
INSERT INTO @Cash ([IdStd], Course, Fees, LearnYear) VALUES (1, 1, '3500', '2019/20')
INSERT INTO @Cash ([IdStd], Course, Fees, LearnYear) VALUES (2, 1, '3000', '2020/21')
INSERT INTO @Cash ([IdStd], Course, Fees, LearnYear) VALUES (3, 1, '3900', '2019/20')
INSERT INTO @Cash ([IdStd], Course, Fees, LearnYear) VALUES (4, 1, '4500', '2018/19')
INSERT INTO @Cash ([IdStd], Course, Fees, LearnYear) VALUES (5, 2, '3500', '2020/21')
INSERT INTO @Cash ([IdStd], Course, Fees, LearnYear) VALUES (6, 2, '3900', '2020/21')
INSERT INTO @Cash ([IdStd], Course, Fees, LearnYear) VALUES (7, 2, '4500', '2019/20')
INSERT INTO @Cash ([IdStd], Course, Fees, LearnYear) VALUES (8, 3, '4500', '2020/21')
--select * from @Cash

select Name, Course, Fees, LearnYear from @Students s inner join @Cash c on s.Id = c.IdStd

I want below result:

Name Course Fees LearnYear Fees LearnYear Fees LearnYear
Rustam 2 3500,00 2019/20 3500,00 2020/21    
Muhammad 1 3000,00 2020/21        
Amirjon 2 3900,00 2019/20 3900,00 2020/21    
Sadriddin 3 4500,00 2018/19 4500,00 2019/20 4500,00 2020/21
Download FREE API for Word, Excel and PDF in ASP.Net: Download