PIVOTING on more than One Col. to get data in given format.

Question

PIVOTING on more than One Col. to get data in given format.

bharat sethi

SSCommitted

Points: 1831
More actions
March 30, 2012 at 11:58 pm

#271258

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86968 More actions · Answer 1

I had to fix some of the column names in your CREATE TABLE to match the columns referenced on the INSERTs and this may not do it the way you seem to prefer (i.e., using a PIVOT) but it does return the rowset you asked for:

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,

[LikeP] [decimal](10, 2) NULL,

[DislikeP] [decimal](11, 2) NULL,

[Like] [varchar](7) NOT NULL,

[Dislike] [varchar](10) NOT NULL,

[Header1] [varchar](2) NOT NULL,

[Header2] [varchar](2) NOT NULL

) ON [PRIMARY]

END

GO

SET ANSI_PADDING OFF

GO