SQL Server Performance

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

Discussion in 'ALL SQL SERVER QUESTIONS' started by bsethi24, Mar 17, 2012.

  1. bsethi24 New Member

    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

Share This Page