PIVOTING on more than One Col. 2 get data in forma

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

PIVOTING on more than One Col. 2 get data in forma

Author

Topic

bsethi24
Starting Member

25 Posts

Posted - 2012-03-17 : 13:15:09

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-03-22 : 03:41:06

See if this helps
http://beyondrelational.com/modules/2/blogs/70/posts/10791/dynamic-crosstab-with-multiple-pivot-columns.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources