How to transpose Row to Col., by using Pivot [Answered]RSS

2 replies

Last post Jan 10, 2019 03:07 AM by ayyappan.CNN

‹ Previous Thread|Next Thread ›

Shortcuts

ayyappan.CNN

Member

317 Points

441 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.

Ayyappan.cn,
Dept of ICT.

Mark post(s) as "Answer" that helped you

limno

All-Star

122763 Points

9869 Posts

Moderator

Re: How to transpose Row to Col., by using Pivot

Jan 07, 2019 04:50 PM|limno|LINK

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]

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

- Reply
ayyappan.CNN
Member

317 Points

441 Posts

Re: How to transpose Row to Col., by using Pivot

Jan 10, 2019 03:07 AM|ayyappan.CNN|LINK

Excellent Iimno,

Thanks

Ayyappan.cn,
Dept of ICT.

Mark post(s) as "Answer" that helped you

‹ Previous Thread|Next Thread ›