locked
How to transpose Row to Col., by using Pivot RRS feed

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

    Monday, January 7, 2019 9:13 AM

Answers

  • 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]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 7, 2019 4:50 PM

All replies

  • 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]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 7, 2019 4:50 PM
  • User-582711651 posted

    Excellent Iimno, 

    Thanks 

    Thursday, January 10, 2019 3:07 AM