This is my Temp.Table 1
CREATE TABLE [dbo].[#StudentMaster](
[StudentID] [int] not NULL,
[StudentName] [varchar](70) NOT NULL,
[StudentDept] [int] NULL,
[Re_ActiveID] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[StudentDept],[Re_ActiveID]) VALUES(101, N'Ramki',701,1)
INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[StudentDept],[Re_ActiveID]) VALUES(102, N'Krishna',701,1)
INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[StudentDept],[Re_ActiveID]) VALUES(103, N'Venkat',702,1)
INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[StudentDept],[Re_ActiveID]) VALUES(104, N'Hari',701,1)
This is my Table 2
CREATE TABLE [dbo].[#Stud_DEPT_TBL](
[DEPT_ID] int NOT NULL,
[DEPT_NAME] [varchar](50) NOT NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[#Stud_DEPT_TBL] ([DEPT_ID],[DEPT_NAME]) VALUES (701, N'Maths')
INSERT [dbo].[#Stud_DEPT_TBL] ([DEPT_ID],[DEPT_NAME]) VALUES (702, N'Science')
INSERT [dbo].[#Stud_DEPT_TBL] ([DEPT_ID],[DEPT_NAME]) VALUES (703, N'H.R.')
INSERT [dbo].[#Stud_DEPT_TBL] ([DEPT_ID],[DEPT_NAME]) VALUES (704, N'Arts & Science')
This is Table 3
CREATE TABLE [dbo].[#Magazine_Master](
[Magazine_ID] int NOT NULL,
[Magazine_Name] [varchar](50) NOT NULL,
[Monthly_Charges] Numeric(5,2) not null,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(1,'India Today',200)
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(2,'The Hindu',150)
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(3,'I.Express',100)
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(4,'Tamil Hindu',200)
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(5,'Computer Today',400)
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(6,'The Science World',300)
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(7,'ISRO (IN)',500)
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(8,'The Nasa (US)',250)
This is table 4
CREATE TABLE [dbo].[#Student_Magazine_Txn](
[Student_ID] int NOT NULL,
[Magazine_ID] int NOT NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(101,1)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(101,2)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(101,3)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(101,4)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(102,4)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(102,5)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,3)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,4)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,5)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,6)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,7)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,8)
This is Select statement
SELECT A.[StudentID],A.[StudentName],[B].[DEPT_NAME], [D].[Magazine_Name],CAST([D].[Monthly_Charges] AS INT) [Rs.]
FROM [dbo].[#StudentMaster] [A] INNER JOIN
[dbo].[#Stud_DEPT_TBL] [B] ON [A].[StudentDept] = [B].[DEPT_ID] INNER JOIN
[dbo].[#Student_Magazine_Txn] [C] ON [A].[StudentID] = [C].[Student_ID] INNER JOIN
[DBO].[#Magazine_Master] [D] ON [C].[Magazine_ID] = [D].[Magazine_ID]
I expect a result like this, how to do this by using Pivot?
CREATE TABLE [dbo].[#StudentMaster](
[StudentID] [int] not NULL,
[StudentName] [varchar](70) NOT NULL,
[StudentDept] [int] NULL,
[Re_ActiveID] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[StudentDept],[Re_ActiveID]) VALUES(101, N'Ramki',701,1)
INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[StudentDept],[Re_ActiveID]) VALUES(102, N'Krishna',701,1)
INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[StudentDept],[Re_ActiveID]) VALUES(103, N'Venkat',702,1)
INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[StudentDept],[Re_ActiveID]) VALUES(104, N'Hari',701,1)
CREATE TABLE [dbo].[#Stud_DEPT_TBL](
[DEPT_ID] int NOT NULL,
[DEPT_NAME] [varchar](50) NOT NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[#Stud_DEPT_TBL] ([DEPT_ID],[DEPT_NAME]) VALUES (701, N'Maths')
INSERT [dbo].[#Stud_DEPT_TBL] ([DEPT_ID],[DEPT_NAME]) VALUES (702, N'Science')
INSERT [dbo].[#Stud_DEPT_TBL] ([DEPT_ID],[DEPT_NAME]) VALUES (703, N'H.R.')
INSERT [dbo].[#Stud_DEPT_TBL] ([DEPT_ID],[DEPT_NAME]) VALUES (704, N'Arts & Science')
CREATE TABLE [dbo].[#Magazine_Master](
[Magazine_ID] int NOT NULL,
[Magazine_Name] [varchar](50) NOT NULL,
[Monthly_Charges] Numeric(5,2) not null,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(1,'India Today',200)
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(2,'The Hindu',150)
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(3,'I.Express',100)
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(4,'Tamil Hindu',200)
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(5,'Computer Today',400)
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(6,'The Science World',300)
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(7,'ISRO (IN)',500)
INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(8,'The Nasa (US)',250)
CREATE TABLE [dbo].[#Student_Magazine_Txn](
[Student_ID] int NOT NULL,
[Magazine_ID] int NOT NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(101,1)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(101,2)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(101,3)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(101,4)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(102,4)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(102,5)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,3)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,4)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,5)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,6)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,7)
INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,8)
;with mycte as (
SELECT A.[StudentID],A.[StudentName],[B].[DEPT_NAME]
, [D].[Magazine_Name],CAST([D].[Monthly_Charges] AS INT) [Rs.]
,row_number() Over(Partition by A.[StudentID],A.[StudentName],[B].[DEPT_NAME] Order by (select null)) rn
From [dbo].[#StudentMaster] [A] INNER JOIN
[dbo].[#Stud_DEPT_TBL] [B] ON [A].[StudentDept] = [B].[DEPT_ID] INNER JOIN
[dbo].[#Student_Magazine_Txn] [C] ON [A].[StudentID] = [C].[Student_ID] INNER JOIN
[DBO].[#Magazine_Master] [D] ON [C].[Magazine_ID] = [D].[Magazine_ID]
)
Select [StudentID], [StudentName], [DEPT_NAME]
,Max(Case when rn=1 then [Magazine_Name] else null end) as Magazine_Name1
,Max(Case when rn=1 then [Rs.] else null end) as [Rs.1]
,Max(Case when rn=2 then [Magazine_Name] else null end) as Magazine_Name2
,Max(Case when rn=2 then [Rs.] else null end) as [Rs.2]
,Max(Case when rn=3 then [Magazine_Name] else null end) as Magazine_Name3
,Max(Case when rn=3 then [Rs.] else null end) as [Rs.3]
,Max(Case when rn=4 then [Magazine_Name] else null end) as Magazine_Name4
,Max(Case when rn=4 then [Rs.] else null end) as [Rs.4]
,Max(Case when rn=5 then [Magazine_Name] else null end) as Magazine_Name5
,Max(Case when rn=5 then [Rs.] else null end) as [Rs.5]
,Max(Case when rn=6 then [Magazine_Name] else null end) as Magazine_Name6
,Max(Case when rn=6 then [Rs.] else null end) as [Rs.6]
FROM
mycte
Group by [StudentID], [StudentName], [DEPT_NAME]
drop TABLE [dbo].[#StudentMaster]
,[dbo].[#Stud_DEPT_TBL]
,[dbo].[#Magazine_Master]
, [dbo].[#Student_Magazine_Txn]
Member
359 Points
526 Posts
How to transpose Row to Col., by using Pivot
Jan 07, 2019 09:13 AM|ayyappan.CNN|LINK
Hi Friends,
This is my Temp.Table 1 CREATE TABLE [dbo].[#StudentMaster]( [StudentID] [int] not NULL, [StudentName] [varchar](70) NOT NULL, [StudentDept] [int] NULL, [Re_ActiveID] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[StudentDept],[Re_ActiveID]) VALUES(101, N'Ramki',701,1) INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[StudentDept],[Re_ActiveID]) VALUES(102, N'Krishna',701,1) INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[StudentDept],[Re_ActiveID]) VALUES(103, N'Venkat',702,1) INSERT [dbo].[#StudentMaster] ([StudentID],[StudentName],[StudentDept],[Re_ActiveID]) VALUES(104, N'Hari',701,1)
This is my Table 2 CREATE TABLE [dbo].[#Stud_DEPT_TBL]( [DEPT_ID] int NOT NULL, [DEPT_NAME] [varchar](50) NOT NULL, ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[#Stud_DEPT_TBL] ([DEPT_ID],[DEPT_NAME]) VALUES (701, N'Maths') INSERT [dbo].[#Stud_DEPT_TBL] ([DEPT_ID],[DEPT_NAME]) VALUES (702, N'Science') INSERT [dbo].[#Stud_DEPT_TBL] ([DEPT_ID],[DEPT_NAME]) VALUES (703, N'H.R.') INSERT [dbo].[#Stud_DEPT_TBL] ([DEPT_ID],[DEPT_NAME]) VALUES (704, N'Arts & Science')
This is Table 3 CREATE TABLE [dbo].[#Magazine_Master]( [Magazine_ID] int NOT NULL, [Magazine_Name] [varchar](50) NOT NULL, [Monthly_Charges] Numeric(5,2) not null, ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(1,'India Today',200) INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(2,'The Hindu',150) INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(3,'I.Express',100) INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(4,'Tamil Hindu',200) INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(5,'Computer Today',400) INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(6,'The Science World',300) INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(7,'ISRO (IN)',500) INSERT [DBO].[#Magazine_Master] ([Magazine_ID],[Magazine_Name],[Monthly_Charges]) VALUES(8,'The Nasa (US)',250)
This is table 4 CREATE TABLE [dbo].[#Student_Magazine_Txn]( [Student_ID] int NOT NULL, [Magazine_ID] int NOT NULL, ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(101,1) INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(101,2) INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(101,3) INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(101,4) INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(102,4) INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(102,5) INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,3) INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,4) INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,5) INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,6) INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,7) INSERT [DBO].[#Student_Magazine_Txn] ([Student_ID],[Magazine_ID]) VALUES(103,8)
This is Select statement SELECT A.[StudentID],A.[StudentName],[B].[DEPT_NAME], [D].[Magazine_Name],CAST([D].[Monthly_Charges] AS INT) [Rs.] FROM [dbo].[#StudentMaster] [A] INNER JOIN [dbo].[#Stud_DEPT_TBL] [B] ON [A].[StudentDept] = [B].[DEPT_ID] INNER JOIN [dbo].[#Student_Magazine_Txn] [C] ON [A].[StudentID] = [C].[Student_ID] INNER JOIN [DBO].[#Magazine_Master] [D] ON [C].[Magazine_ID] = [D].[Magazine_ID]
I expect a result like this, how to do this by using Pivot?
StudentID
StudentName
DEPT_NAME
Magazine_Name
Rs.
Magazine_Name
Rs.
Magazine_Name
Rs.
Magazine_Name
Rs.
Magazine_Name
Rs.
Magazine_Name
Rs.
101
Ramki
Maths
India Today
200
The Hindu
150
I.Express
100
Tamil Hindu
200
102
Krishna
Maths
Tamil Hindu
200
Computer Today
400
103
Venkat
Science
I.Express
100
Tamil Hindu
200
Computer Today
400
The Science World
300
ISRO (IN)
500
The Nasa (US)
250
Please help me,
Thanks in advance.
Dept of ICT.
Mark post(s) as "Answer" that helped you
All-Star
123252 Points
10024 Posts
Moderator
Re: How to transpose Row to Col., by using Pivot
Jan 07, 2019 04:50 PM|limno|LINK
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Member
359 Points
526 Posts
Re: How to transpose Row to Col., by using Pivot
Jan 10, 2019 03:07 AM|ayyappan.CNN|LINK
Excellent Iimno,
Thanks
Dept of ICT.
Mark post(s) as "Answer" that helped you