How to transpose Row to Col., by using Pivot

Question

User-582711651 posted

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. 

Answer 1

User77042963 posted

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]

Answer 2

User-582711651 posted

Excellent Iimno, 

Thanks