Dear All, Hi! I am searching a solution to PIVOT more than one column to get output in below mentioned format. I have a table which has data in following format: - QNo questionid QuestionName DisplayOrder For4andabove For3andbelow3 TotalCandidates Like DisLike Like DisLike Header1 Header2 Q1 1 Clarity 1 3 2 5 60 40 Like DisLike H1 H2 Q2 2 Logistics 2 1 4 5 20 80 Like DisLike H1 H2 Q3 3 Fitness 3 1 4 5 20 80 Like DisLike H1 H2 Q4 4 Recommend 4 2 3 5 40 60 Like DisLike H1 H2 Q5 5 Cure 5 5 0 5 100 0 Like DisLike H1 H2 Q6 6 Improvement 6 1 4 5 20 80 Like DisLike H1 H2 Q7 7 Opinions 7 2 3 5 40 60 Like DisLike H1 H2 Q8 8 Reason 8 1 4 5 20 80 Like DisLike H1 H2 Q9 9 Quality 9 3 2 5 60 40 Like DisLike H1 H2 Q10 10 Best Friend 10 1 4 5 20 80 Like DisLike H1 H2 Q11 11 Meetings 11 1 4 5 20 80 Like DisLike H1 H2 Q12 12 Learning 12 1 4 5 20 80 Like DisLike H1 H2 & I need this data in following format: - Label Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Header1 Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Header2 Clarity Logistics Fitness Recommend Cure Improvement Opinions Reason Quality Best Friend Meetings Learning Like 60% 20% 20% 40% 100% 20% 40% 20% 60% 20% 20% 20% DisLike 40% 80% 80% 60% 0% 80% 60% 80% 40% 80% 80% 80% How do I achieve the same without compromising in performance??? I achieved the part of same by applying PIVOTING on ONE Column by following four queries: - --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- DECLARE @Sql1 VARCHAR(MAX), @ColumnList1 VARCHAR(max) SELECT @ColumnList1 = COALESCE( @ColumnList1 + '], [' + convert (Varchar(max), QNo), convert (Varchar(max), QNo), @ColumnList1 ) FROM testing set @ColumnList1 = '['+@ColumnList1+']' --select @ColumnList1 if (@ColumnList1 is not null and @ColumnList1!= '' and @ColumnList1 not like '%[]%') begin SET @Sql1 = ' select distinct * from ( select Header1, ISNULL (QNo, '''') as QNo from testing ) as tab pivot (max(QNo) for QNo in ( '+ @ColumnList1 + ') ) as pvt --order by Header1 ' print @Sql1 EXEC (@Sql1) end --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- DECLARE @Sql2 VARCHAR(MAX), @ColumnList2 VARCHAR(max) SELECT @ColumnList2 = COALESCE( @ColumnList2 + '], [' + convert (Varchar(max), QNo), convert (Varchar(max), QNo), @ColumnList2 ) FROM testing set @ColumnList2 = '['+@ColumnList2+']' --select @ColumnList2 if (@ColumnList2 is not null and @ColumnList2!= '' and @ColumnList2 not like '%[]%') begin SET @Sql2 = ' select distinct * from ( select Header2, QNo, ISNULL (QuestionName, '''') as QuestionName from testing ) as tab pivot (max(QuestionName) for QNo in ( '+ @ColumnList2 + ') ) as pvt --order by Header2 ' print @Sql2 EXEC (@Sql2) end --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- DECLARE @Sql3 VARCHAR(MAX), @ColumnList3 VARCHAR(max) SELECT @ColumnList3 = COALESCE( @ColumnList3 + '], [' + convert (Varchar(max), QNo), convert (Varchar(max), QNo), @ColumnList3 ) FROM testing set @ColumnList3 = '['+@ColumnList3+']' --select @ColumnList3 if (@ColumnList3 is not null and @ColumnList3!= '' and @ColumnList3 not like '%[]%') begin SET @Sql3 = ' select distinct * from ( select Like, QNo, ISNULL (LikeP, 0) as LikeP from testing ) as tab pivot (max(LikeP) for QNo in ( '+ @ColumnList3 + ') ) as pvt --order by Like ' print @Sql3 EXEC (@Sql3) end --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- DECLARE @Sql4 VARCHAR(MAX), @ColumnList4 VARCHAR(max) SELECT @ColumnList4 = COALESCE( @ColumnList4 + '], [' + convert (Varchar(max), QNo), convert (Varchar(max), QNo), @ColumnList4 ) FROM testing set @ColumnList4 = '['+@ColumnList4+']' --select @ColumnList4 if (@ColumnList4 is not null and @ColumnList4!= '' and @ColumnList4 not like '%[]%') begin SET @Sql4 = ' select distinct * from ( select DisLike, QNo, ISNULL (DisLikeP, 0) as DisLikeP from testing ) as tab pivot (max(DisLikeP) for QNo in ( '+ @ColumnList4 + ') ) as pvt --order by DisLike ' print @Sql4 EXEC (@Sql4) end --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- How can I achieve the same? Is there more than one solution for such working & what is the best one to achieve the same?? --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- To populate this data in SQL you can use below CREATE TABLE & INSERT SCRIPTS --<><>-- ------------------------------------- -- CREATE TABLE SCRIPT -- USE [TestingDB] GO /****** Object: Table [dbo].[testing] Script Date: 03/17/2012 21:28:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[testing]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[testing]( [QNo] [varchar](1001) NULL, [questionid] [int] NOT NULL, [QuestionName] [varchar](max) NOT NULL, [DisplayOrder] [int] NOT NULL, [For4andabove] [int] NULL, [For3andbelow3] [int] NULL, [TotalEmp] [int] NULL, [EngagedP] [decimal](10, 2) NULL, [DisengagedP] [decimal](11, 2) NULL, [Engaged] [varchar](7) NOT NULL, [DisEngaged] [varchar](10) NOT NULL, [Header1] [varchar](2) NOT NULL, [Header2] [varchar](2) NOT NULL ) ON [PRIMARY] END GO SET ANSI_PADDING OFF GO ------------------------------------- ------------------------------------- -- INSERT SCRIPTS to populate the data in above CREATED Table -- INSERT INTO [TESTING] ( [QNo], [questionid], [QuestionName], [DisplayOrder], [For4andabove], [For3andbelow3], [TotalEmp], [LikeP], [DisLikeP], [Like], [DisLike], [Header1], [Header2] ) VALUES ( N'Q1', 1,N'Clarity', 1, 3, 2, 5, 60.00, 40.00,N'Like',N'DisLike',N'H1',N'H2' ); INSERT INTO [TESTING] ( [QNo], [questionid], [QuestionName], [DisplayOrder], [For4andabove], [For3andbelow3], [TotalEmp], [LikeP], [DisLikeP], [Like], [DisLike], [Header1], [Header2] ) VALUES ( N'Q2', 2,N'Logistics', 2, 1, 4, 5, 20.00, 80.00,N'Like',N'DisLike',N'H1',N'H2' ); INSERT INTO [TESTING] ( [QNo], [questionid], [QuestionName], [DisplayOrder], [For4andabove], [For3andbelow3], [TotalEmp], [LikeP], [DisLikeP], [Like], [DisLike], [Header1], [Header2] ) VALUES ( N'Q3', 3,N'Fitness', 3, 1, 4, 5, 20.00, 80.00,N'Like',N'DisLike',N'H1',N'H2' ); INSERT INTO [TESTING] ( [QNo], [questionid], [QuestionName], [DisplayOrder], [For4andabove], [For3andbelow3], [TotalEmp], [LikeP], [DisLikeP], [Like], [DisLike], [Header1], [Header2] ) VALUES ( N'Q4', 4,N'Recommend', 4, 2, 3, 5, 40.00, 60.00,N'Like',N'DisLike',N'H1',N'H2' ); INSERT INTO [TESTING] ( [QNo], [questionid], [QuestionName], [DisplayOrder], [For4andabove], [For3andbelow3], [TotalEmp], [LikeP], [DisLikeP], [Like], [DisLike], [Header1], [Header2] ) VALUES ( N'Q5', 5,N'Cure', 5, 5, 0, 5, 100.00, 0.00,N'Like',N'DisLike',N'H1',N'H2' ); INSERT INTO [TESTING] ( [QNo], [questionid], [QuestionName], [DisplayOrder], [For4andabove], [For3andbelow3], [TotalEmp], [LikeP], [DisLikeP], [Like], [DisLike], [Header1], [Header2] ) VALUES ( N'Q6', 6,N'Improvement', 6, 1, 4, 5, 20.00, 80.00,N'Like',N'DisLike',N'H1',N'H2' ); INSERT INTO [TESTING] ( [QNo], [questionid], [QuestionName], [DisplayOrder], [For4andabove], [For3andbelow3], [TotalEmp], [LikeP], [DisLikeP], [Like], [DisLike], [Header1], [Header2] ) VALUES ( N'Q7', 7,N'Opinions', 7, 2, 3, 5, 40.00, 60.00,N'Like',N'DisLike',N'H1',N'H2' ); INSERT INTO [TESTING] ( [QNo], [questionid], [QuestionName], [DisplayOrder], [For4andabove], [For3andbelow3], [TotalEmp], [LikeP], [DisLikeP], [Like], [DisLike], [Header1], [Header2] ) VALUES ( N'Q8', 8,N'Reason', 8, 1, 4, 5, 20.00, 80.00,N'Like',N'DisLike',N'H1',N'H2' ); INSERT INTO [TESTING] ( [QNo], [questionid], [QuestionName], [DisplayOrder], [For4andabove], [For3andbelow3], [TotalEmp], [LikeP], [DisLikeP], [Like], [DisLike], [Header1], [Header2] ) VALUES ( N'Q9', 9,N'Quality', 9, 3, 2, 5, 60.00, 40.00,N'Like',N'DisLike',N'H1',N'H2' ); INSERT INTO [TESTING] ( [QNo], [questionid], [QuestionName], [DisplayOrder], [For4andabove], [For3andbelow3], [TotalEmp], [LikeP], [DisLikeP], [Like], [DisLike], [Header1], [Header2] ) VALUES ( N'Q10', 10,N'Best Friend', 10, 1, 4, 5, 20.00, 80.00,N'Like',N'DisLike',N'H1',N'H2' ); INSERT INTO [TESTING] ( [QNo], [questionid], [QuestionName], [DisplayOrder], [For4andabove], [For3andbelow3], [TotalEmp], [LikeP], [DisLikeP], [Like], [DisLike], [Header1], [Header2] ) VALUES ( N'Q11', 11,N'Meetings', 11, 1, 4, 5, 20.00, 80.00,N'Like',N'DisLike',N'H1',N'H2' ); INSERT INTO [TESTING] ( [QNo], [questionid], [QuestionName], [DisplayOrder], [For4andabove], [For3andbelow3], [TotalEmp], [LikeP], [DisLikeP], [Like], [DisLike], [Header1], [Header2] ) VALUES ( N'Q12', 12,N'Learning', 12, 1, 4, 5, 20.00, 80.00,N'Like',N'DisLike',N'H1',N'H2' ); GO ------------------------------------- --<><>-- --<><>-- --<><>-- --<><>-- Thanks & Regards, BSethi24