none
How to create a Pivot table with a Column of Multiple rows?

    Question

  • Hi!

    Can we create pivot table with Multiple columns and each column contains multiple rows.

    For example...........

    Database Table:

    BatchID    BatchName      Chemical      Value
    
    --------------------------------------------------------
    BI-1          BN-1       CH-1             1
    
    BI-2          BN-2       CH-2             2
    
    --------------------------------------------------------
    

    This is the table  i need to display.                     

                          BI-1          BI-2
    
                          BN-1          BN-2
    -----------------------------------------
    
    CH-1                   1            null
    ------------------------------------------
    
    CH-2                   null          2
    ------------------------------------------
    

    Here BI-1,BN-1 are two rows in a single columns i need to display chemical value as row of that.

    Could Please help me to solve this problem.

    Thank You.

    Monday, March 22, 2010 8:49 AM

Answers

  • If you are using Reporting server then you can design cross tab report having multiple columns in header.

    Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    • Proposed as answer by Shamas Saeed Monday, March 22, 2010 10:02 AM
    • Marked as answer by G Kiran Kumar Tuesday, March 23, 2010 6:10 AM
    • Unmarked as answer by G Kiran Kumar Tuesday, March 23, 2010 6:10 AM
    • Marked as answer by G Kiran Kumar Tuesday, March 23, 2010 12:39 PM
    Monday, March 22, 2010 9:40 AM

All replies

  • Hi Irsgkiran,

    What about the following query, actually it is a transpose of your requirement.

    SELECT
      *
    FROM (
      SELECT * FROM SQLPivot
    ) AS TableAlias
    PIVOT
    (
      SUM(Value)
      FOR [Chemical]
      IN (
        [CH-1],[CH-2]
      )
    ) AS PivotTableAlias
    

    You can find my notes on T-SQL Pivot Tables in SQL Server

    Actually I'm not sure we can build a second layer using pivots. But I will try to figure it out.

     


    http://www.kodyaz.com My Personal Site Our true mentor in life is science
    Monday, March 22, 2010 9:21 AM
  • In fact, it is not possible to have "multiple row" column header in t-sql. It is only possible in a mdx query from cube.

    What at best you can do is try to have a column header which is a concatenated string of the BN and BI columns.

     


    Maeenul
    My Programming Site
    My Blog
    Monday, March 22, 2010 9:28 AM
  • If you are using Reporting server then you can design cross tab report having multiple columns in header.

    Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    • Proposed as answer by Shamas Saeed Monday, March 22, 2010 10:02 AM
    • Marked as answer by G Kiran Kumar Tuesday, March 23, 2010 6:10 AM
    • Unmarked as answer by G Kiran Kumar Tuesday, March 23, 2010 6:10 AM
    • Marked as answer by G Kiran Kumar Tuesday, March 23, 2010 12:39 PM
    Monday, March 22, 2010 9:40 AM
  • Thank you, for your reply
    Monday, March 22, 2010 10:00 AM
  • please mark as answer.

    Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    Monday, March 22, 2010 10:02 AM
  • Sorry i did not get appropriate solution to my problem upto now.

    And i am not familiar with Reporting services , so please give me some time to check it first.

    Monday, March 22, 2010 1:27 PM
  • Could please guide me furthur more to solve this problem in the terms of code.

    Thanks In advance.

    Tuesday, March 23, 2010 12:39 PM
  • Hi,

     

    If you are not familiar with the reporting services. Don't wory

     

    You can easily learn from this link http://balavardhanreddy.over-blog.com/categorie-11295031.html

     

    Now after creating single column report you can manually add as much columns and rows as you need.

     

    If you still find problem please mail me on shamassaeedmr@hotmail.com and i will give you a report design for your problem solution.


    Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    Thursday, March 25, 2010 5:00 AM