SQL SERVER – PIVOT and UNPIVOT Table Examples

I previously wrote two articles about PIVOT and UNPIVOT tables. I really enjoyed writing about them as it was interesting concept. One of the Jr. DBA at my organization asked me following question.

“If we PIVOT any table and UNPIVOT that table do we get our original table?”

I really think this is good question. Answers is Yes, you can but not always. When we pivot the table we use aggregated functions. If due to use of this function if data is aggregated, it will be not possible to get original data back.

Let me explain this issue demonstrating simple example.
USE AdventureWorks
GO
-- Creating Test Table
CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO
-- Selecting and checking entires in table
SELECT *
FROM Product
GO
-- Pivot Table ordered by PRODUCT
SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
(SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT
GO
-- Pivot Table ordered by CUST
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
(SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt
ORDER BY CUST
GO
-- Unpivot Table ordered by CUST
SELECT CUST, PRODUCT, QTY
FROM
(
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
UNPIVOT
(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
)
AS Unpvt
GO
-- Clean up database
DROP TABLE Product
GO

ResultSet:
-- Selecting and checking entires in table
Cust Product QTY
------------------------- -------------------- -----------
KATE VEG 2
KATE SODA 6
KATE MILK 1
KATE BEER 12
FRED MILK 3
FRED BEER 24
KATE VEG 3

-- Pivot Table ordered by PRODUCT
PRODUCT FRED KATE
-------------------- ----------- -----------
BEER 24 12
MILK 3 1
SODA NULL 6
VEG NULL 5

-- Pivot Table ordered by CUST
CUST VEG SODA MILK BEER CHIPS
------------------------- ----------- ----------- ----------- ----------- -----------
FRED NULL NULL 3 24 NULL
KATE 5 6 1 12 NULL

-- Unpivot Table ordered by CUST
CUST PRODUCT QTY
------------------------- -------- -----------
FRED MILK 3
FRED BEER 24
KATE VEG 5
KATE SODA 6
KATE MILK 1
KATE BEER 12
12


You can see in above example where we are using the SUM aggregated functions. SUM adds up values based on column used in the sum function. In our example Kate and Veg has two entries. In our pivot example with order by Cust the values are summed up. Now when table goes under UNPIVOT operations it transforms the table which is already went under PIVOT operation.

Looking at the final PIVOT – UNPIVOT table is little different from the original table and it contains the sum of the two records which we have observed in the PIVOT table. You can see that result which are displayed in red fonts are summed.

This way we can get the original table back if aggregate functions was not applied on the data or data was in such form that aggregate function might have not made any difference.

Reference : Pinal Dave (http://blog.SQLAuthority.com), SQL SERVER – UNPIVOT Table Example, SQL SERVER – PIVOT Table Example

About these ads

111 thoughts on “SQL SERVER – PIVOT and UNPIVOT Table Examples

    • I have a requirement in which after Pivot, I don’t want the aggregated value, instead I need ‘Y’ and for Null values it should be ‘N’. How can I achieve this? Please help me.

  1. fine mr!…
    can we write a select statement in IN OF PIVOT

    PIVOT
    ( SUM(QTY)
    FOR PRODUCT
    IN (here i need to write select statement can we do it)
    ) AS pvt)

    waiting 4 replay

  2. Hi,

    Great article!

    I have a question, is there any way to unpivot a table with different data types without having to cast every single column into a common data type?

    In your example and most examples I have seen writers use integers but if I need to unpivot a table with several columns with different data types (integers, nvarchars of different sizes, etc.) I have to cast every column into nvarchar. Is there a quick way to do this? Maybe part of the unpivot syntax?

  3. I am having a problem with the Pivot…is there something wrong with my syntax…it just returns the column names as values…

    Select BidId,’Cancelled’,
    ‘In Progress’,
    ‘Loss’,
    ‘New’,
    ‘No Bid’,
    ‘Referred Out’
    FROM (
    Select bi.BidId, dbo.Status.Name, dbo.StatusComments.CommentDate
    From Bids bi INNER JOIN
    dbo.BidFormOwning ON bi.BidId = dbo.BidFormOwning.BidId INNER JOIN
    dbo.BidRevision ON dbo.BidFormOwning.BidFormOwningId = dbo.BidRevision.BidFormOwningId INNER JOIN
    dbo.StatusComments ON dbo.BidRevision.BidRevisionId = dbo.StatusComments.RevisionId INNER JOIN
    dbo.SupportTeams ON dbo.BidFormOwning.SupportTeamId = dbo.SupportTeams.SupportTeamId INNER JOIN
    dbo.Status ON dbo.StatusComments.StatusId = dbo.Status.StatusId
    where dbo.BidFormOwning.SupportTeamId=2
    and dbo.Status.Name in (‘Cancelled’,
    ‘In Progress’,
    ‘Loss’,
    ‘New’,
    ‘No Bid’,
    ‘Referred Out’)) as a
    Pivot
    (
    Count(CommentDate)
    FOR Name In ([Cancelled],
    [In Progress],
    [Loss],
    [New],
    [No Bid],
    [Referred Out])) as b
    order by BidId

  4. Pingback: SQL SERVER - Example of PIVOT UNPIVOT Cross Tab Query in Different SQL Server Versions Journey to SQL Authority with Pinal Dave

  5. Hi Dave,

    It is really interesting concept. I would like to learn more about this concept. Where can I get more information about PIVOT and UNPIVOT?

  6. Hi,
    I have two Columns State and City. When I select them from table, it displays like
    STATE CITY
    Connecticut Hartford
    Connecticut Stamford
    Connecticut New Haven
    Connecticut Bridgeport
    But I need them as
    STATE CITY1 CITY2 CITY3 CITY4
    Connecticut Hartford Stamford New Haven Bridgeport
    So that I can use them in my SP. There are many cities for some states.
    Can you please help me out by using Pivot?

  7. hi

    iam new to this website.i want to learn sqlserver.2005 or 2008 which one is better.can any one give an idea how to start and where to start.plz help me
    thanks in advance

  8. I have a table with two columns, labeled Year and Loss. In the Year
    field, I have the numbers 1 to 10,000, each which can or cannot
    repeat. In the Loss column, i have numbers corresponding to the
    Years…for example:

    Year, Loss
    1, 568
    1, 621
    1, 358
    1, 7888
    2, 2689
    2, 6563
    2, 15
    3, 983
    3, 146
    3, 258
    3, 852
    4, 96
    5, 87
    5, 32

    So, you see, Year 1 can have four losses, Year 2 can have three
    losses, etc.

    Now, here’s where I need help as I’m not sure what to do given that I
    am a beginner at SQl and learning: I want to have just one row for
    each Year. So, for Year 1, I would like to have four extra columns,
    and for Year 2, I would like to have three columns created to hold
    loss numbers so they display in a row – please see below for example:

    Year, Loss1, Loss2, Loss3, Loss4, Loss5, etc.
    1, 568, 621, 358, 7888
    2, 2689, 6563, 15
    3, 983, 146, 258, 852
    4, 96
    5, 87, 32

    I hope that the examples helped in clarifying what kind of Query I
    need.
    plz send me solution

  9. hi.. i have a table tblData containing columns fanSerialNo,d1,d2,d3,d4..

    this is how the table return values

    fanSerialNo d1 d2 d3 d4
    101 10 11 12 13

    but i want the resultset like

    fanserialNo days qty
    101 d1 10
    101 d2 11
    101 d3 12
    101 d4 13

    Can anyone help me??
    Thanx

  10. article hold great content regarding pivot unpivot but the question always arises in my mind is that what is the exact defination of a Pivot and Unpivot table…..

  11. Hi

    I need to do the same PIVOT functionality in Sql 2000, can some one help me

    Eg:
    Item cp amnt
    A R1 10
    A R2 10
    B R1 4
    B R2 5

    I need this to be formatted like

    Item R1 R2
    A 10 10
    B 4 5

    Thanks

    • Hi

      I know It’s too late to answer you but this ans will be useful for future help to others

      create table _temp (item nvarchar(15),cp nvarchar(10),amnt float)
      insert into _temp values(‘A’,’R1′,10)
      insert into _temp values(‘A’,’R2′,10)
      insert into _temp values(‘B’,’R1′,4)
      insert into _temp values(‘B’,’R2′,5)
      select * from _temp

      select item,[R1],[R2]
      from ( select cp,item,amnt from _temp )as s1
      pivot
      (sum(amnt) for cp in ([R1],[R2])) as pivottable

  12. Hi,

    i have table like below ,

    Product Name Price Date
    Apple 1.5 5/5/2009
    Apple 3 5/6/2009
    Apple 3.5 5/7/2009
    Apple 2.5 5/8/2009
    Apple 5.5 5/9/2009
    Orange 10.5 5/5/2009
    Orange 12.5 5/6/2009
    Orange 7.5 5/7/2009
    Orange 4.5 5/8/2009
    Orange 5.5 5/9/2009

    I need output like below

    Product Name 5/5/2009 5/6/2009 5/7/2009 5/8/2009 5/9/2009

    Apple 1.5 3 3.5 2.5 5.5
    Orange 10.5 12.5 7.5 4.5 5.5

    also date increases column also need to increase,
    Pls help me

    Vickees

  13. Hi guys,

    i have table like below ,

    Product Name Price Date
    Apple 1.5 5/5/2009
    Apple 3 5/6/2009
    Apple 3.5 5/7/2009
    Apple 2.5 5/8/2009
    Apple 5.5 5/9/2009
    Orange 10.5 5/5/2009
    Orange 12.5 5/6/2009
    Orange 7.5 5/7/2009
    Orange 4.5 5/8/2009
    Orange 5.5 5/9/2009

    I need output like below

    Product Name 5/5/2009 5/6/2009 5/7/2009 5/8/2009 5/9/2009

    Apple 1.5 3 3.5 2.5 5.5
    Orange 10.5 12.5 7.5 4.5 5.5

    also date increases column also need to increase,
    Pls help me

    Vickees

  14. can we write a select statement in IN OF PIVOT

    PIVOT
    ( SUM(QTY)
    FOR PRODUCT
    IN (here i need to write select statement can we do it)
    ) AS pvt)

    waiting 4 replay

  15. Hi,

    I have a query regarding the cross tab query. I am generating a report in excel format e.g. survey report. curently I have create a stored procedure for that, everything is working fine for those 5 option for the survey question (Agree,disagree,neutral etc..) I have specified in the stored procedure,Now if admin enter one more option for the survey question answer, Now I don’t want admin to go to the procedure code and from dotnet code to chnage everything.

    Can We do this dynamically sso that when a admin add a new option for any wuestion then it should automaticalyyy display that option.

    Can Anyone suggest me how to do that I here by pasting the code for my procedure..

    SELECT P1.*, (P1.Option1 + P1.Option2 + P1.Option3 + P1.Option4 + P1.Option5) AS TotalVotes
    FROM (SELECT
    Surveys.Question,
    –SurveyResults.SurveyOptionID,
    Option1 = COUNT(CASE WHEN SurveyOptions.OptionName=’STRONGLY AGREE’ THEN SurveyResults.SurveyOptionID END),
    Option2 = COUNT(CASE WHEN SurveyOptions.OptionName=’AGREE’ THEN SurveyResults.SurveyOptionID END),
    Option3 = COUNT(CASE WHEN SurveyOptions.OptionName=’NEUTRAL’ THEN SurveyResults.SurveyOptionID END),
    Option4 = COUNT(CASE WHEN SurveyOptions.OptionName=’DISAGREE’ THEN SurveyResults.SurveyOptionID END),
    Option5 = COUNT(CASE WHEN SurveyOptions.OptionName=’STRONGLY DISAGREE’ THEN SurveyResults.SurveyOptionID END)

    /*Surveys.OptionType,*/
    –SurveyResults.UserID
    –SurveyOptions.OptionName
    –SurveyOptions.IsCorrect

    FROM
    SurveyResults INNER JOIN
    SurveyOptions ON SurveyResults.SurveyOptionID = SurveyOptions.SurveyOptionID INNER JOIN
    Surveys ON SurveyOptions.SurveyID = Surveys.SurveyID

    WHERE
    (Surveys.ModuleID = @ModuleID)
    and
    (SurveyResults.UserID 0)
    and
    (Convert(varchar(10),SurveyResults.SurveyDate,101) >=@FromDate
    and Convert(varchar(10),SurveyResults.SurveyDate,101) <=@ToDate )

    GROUP BY Surveys.Question,Surveys.SurveyID ) AS P1

  16. @sansugoi

    Since you did not mention, any input, I assumed input as STRONGLY AGREE,AGREE,NEUTRAL,DISAGREE. You can change it how ever you want.

    Please follow the order of the input, comma seperated with no spaces. otherwise procedure might not execute.

    So this is the procedure, check this out, and let us know if you have questions. at the end of procedures, I have given, samples to execute, take a look of it.

    — Start of procedure.

    Create Procedure USP_Generate_DynamicSQL (@OptionCompleteName varchar(8000) )
    AS
    SET NOCOUNT ON

    — First Lets seperate out comma seperated values and store in a table variable.
    Declare @table table (Ident int Identity , OptionName varchar(40))
    Declare @Length int
    Declare @Store_Name varchar (40)

    — Start of Storing comma seperated values into a table variable

    Set @length = 1

    While @length != 0
    begin
    Set @Store_Name = NULL
    Set @length = 1
    Set @Store_Name = substring ( @OptionCompleteName , @length, case when (charindex (‘,’ , @OptionCompleteName)-1)= -1 then len(@OptionCompleteName) else (charindex (‘,’ , @OptionCompleteName)-1) End )

    insert into @table values (@Store_Name )

    set @length = (charindex (‘,’ , @OptionCompleteName)+1)

    If @length != 1
    begin
    Set @OptionCompleteName = substring ( @OptionCompleteName, @length, datalength (@OptionCompleteName) + 1 – @length)
    set @length = (charindex (‘,’ , @OptionCompleteName)+1)
    End

    Else
    Begin
    Set @OptionCompleteName = substring ( @OptionCompleteName , 1, datalength (@OptionCompleteName))
    Set @length = 0
    End

    End

    — End of Storing comma seperated values into a table variable

    — Lets build the first short select statement dynamically.
    Declare @Select_Statement1 varchar(8000)
    Set @Select_Statement1 = ”

    select @Select_Statement1 = @Select_Statement1 +case when @Select_Statement1 = ” then ” else ‘+’ End +’P1.Option’+convert (varchar, Ident)
    from @table

    — print @Select_Statement1

    — Now lets build our final select statement dynamically.

    Declare @Select_Statement2 varchar(8000)

    Set @Select_Statement2 = ‘

    SELECT P1.*, (‘+@Select_Statement1+’) AS TotalVotes
    FROM (
    SELECT
    Surveys.Question’

    Declare @count int
    Set @count = 1

    Declare @Ident int
    Declare @OptionName varchar(40)
    While @Count =@FromDate
    and Convert(varchar(10),SurveyResults.SurveyDate,101) <=@ToDate )
    GROUP BY Surveys.Question,Surveys.SurveyID ) AS P1'

    print @Select_Statement2

    SET NOCOUNT OFF

    GO
    — Sample to Execute
    Exec USP_Generate_DynamicSQL 'STRONGLY AGREE,AGREE,NEUTRAL,DISAGREE,STRONGLY'

    Exec USP_Generate_DynamicSQL 'STRONGLY AGREE,AGREE,NEUTRAL,DISAGREE'

    ~ IM

  17. Hi Guys,
    I have the following 4 records which reflect the price of a product in 4 different stores.

    Rec # Commodity_Name Product_Description Product_ID Price
    1 AIRFRESHNER 200ML MORNING MIST 31 2.99
    2 AIRFRESHNER 200ML MORNING MIST 42 1.99
    3 AIRFRESHNER 200ML MORNING MIST 83 3.99
    4 AIRFRESHNER 200ML MORNING MIST 94 4.99

    Is it possible to display the above fields for only record 1 and only the price fields for records 2, 3 and 4 on the same line using the Pivot Statement?

    I’m using SQL Server 2008 and just wondering if there much of an overhead using the Pivot Statement??

    Thanks.

  18. Hi All,

    I want to combine 3 columns into one column. It must group by Col_1 and then gourp by Col_2. I am just wondering if there is easier way to do it. Thanks.

    Create table tbl(Col_1 varchar(50),Col_2 varchar(50), Col_3 varchar(50))
    Insert into tbl values(‘A’,’B’,’C’)
    Insert into tbl values(‘A’,’B’,’D’)
    Insert into tbl values(‘A’,’B’,’E’)
    Insert into tbl values(‘A’,’B1′,’F’)
    Insert into tbl values(‘A’,’B1′,’G’)
    Insert into tbl values(‘A’,’B1′,’H’)
    Insert into tbl values(‘A’,’B2′,’I’)
    Insert into tbl values(‘A’,’B2′,’J’)
    Insert into tbl values(‘A’,’B2′,’L’)
    Insert into tbl values(‘A1′,’B’,’M’)
    Insert into tbl values(‘A1′,’B1′,’N’)

    One_Col
    A
    B
    C
    D
    E
    B1
    F
    G
    H
    B2
    I
    G
    L
    A1
    B
    M
    A1
    B1
    N

    Drop table tbl;

  19. I have calendar table which has statdate, enddate, QTR ect. data. We are pulling Orders by QTR. I like to create a a cross-tab report which looks like this. If it possible to do this. Please help…..

    FY10 FY09 FY08 FY07 FY09
    Q1 5 15 17 18 40
    Q2 0 2 22 21 10
    Q3 55 11 10 8 5
    Q4 5 1 2 0

    Total

  20. Hi,

    I am using SQL Server 2000 in one of my project and querying one issue.

    i.e.

    Is there any alternate solution of using PIVOT/UNPIVOT in SQL Server 2000??

    Regards
    Raj Sukhija

  21. Hi,
    I have data in table in following format:-
    StatsName StatsValue
    Sample(S) S(1)
    BPI-Sys 48
    BPD-DIA 17
    HT 153
    WT 68
    Age 28
    BMI 77
    Resting Pulse 88
    Total Colestral 99
    LDL 18
    HDL 17
    Sample(S) S(2)
    BPI-Sys 8
    BPD-DIA 7
    HT 8
    WT 9
    Age 8
    BMI 7
    Resting Pulse 8
    Total Colestral 9
    LDL 8
    HDL 7

    And I want result in following format using Pivot:-

    Sample(S) BPI-Sys BPD-DIA HT WT Age BMI Resting Pulse Total Colestral LDL HDL
    S(1) 48 17 153 68 28 77 88 99 18 17
    S(2) 8 7 8 9 8 7 8 9 8 7

    How to do it please help me.

  22. Hi,
    I need a Solution for the Following

    I have two Tables with following Structure
    ——————————

    Table : Allowance
    ————————————–
    Id Name CountryID
    ————————————–
    1 Allowance 1 1
    1 Allowance 1 2
    2 Allowance 2 3
    2 Allowance 2 2
    ……………………………………..
    ………………………………………
    —————————–
    Table : Country
    ————————–
    ID CountryName
    ————————-
    1 India
    2 United States
    3 Japan
    ………………………..
    ……………………….
    ——————–

    Using following PIVOT query I got the out put
    ———————————————————
    SELECT * FROM
    (select A.Name,CountryID as CountryID,C.Name as Country
    from Allowance as A
    left join Country As C on(A.CountryID=C.Id))AS Temp1
    PIVOT (max(Country) For CountryID in([1],[2],[3])) as X
    ———————————————————————
    OutPut
    ——————————————————————–
    NAME 1 2 3
    ——————————————————————–
    Allowance 1 India United States NULL
    Allowance 2 NULL United States Japan
    ——————————————————————-
    Here I got only 3 COUNTRY Columns ,But I need N number of Columns with name like Country1 … Country2…..country..N
    How it is possible ,
    Please help me.

    Thanks & kind regards
    Mohandas Pk

  23. 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 like below in Excel Sheet

    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.

  24. I need to display data in dashboard by week date in column but date would be change not fix , user can change different week date and dynamically see data , is it possible using pivot or any other way to do.

  25. i could not find on web solution for this but i found myself , i use cursor and update column in horizontal , thanks

  26. I have a table in the follwing format

    UserId Email TrainerName NoOfPatients
    1 x a 1
    2 y b 2
    3 z c 3

    How do i get tha table in format below

    a b c
    UserID
    Email
    NoOfPatients

  27. Friends,
    I hope in the comming versions of SQL we will have the pivot in a simpler syntax.

    I am not complaining, it has got much better in 2005, compared to 2000.

    But I hope to have some thing like
    select field_to_group from myTable pivot by verCol, horCol

  28. Hi,

    I am using pivot query in table. but i don’t require any order by sorting in that. when i remove order by from pivot query. still it make order by on Product.

    • @Vishal

      Can you use OVER Partition Function in your select statement inside PIVOT to create a unique number for every record, the order will be as is. And then order by that unique number when displaying, so you will see records in the order they were present inside the database.

      Let us know if you need more info, Also next time please share sample script with sample input data and expected output

      ~Peace.

      • Hi Imran,

        Thanks for reply.

        Look at the above example of the blog. in this scenario if you remove order by “PRODUCT” then it still sort record by product.

        And as you suggest i am using Partition function it will not display proper result. i.e. This will create 2 rows for “BEER”. In first row it display record for “FRED” and in Second row display record for “KATE”.

        The query which i tried is:

        SELECT PRODUCT, FRED, KATE, RANK
        FROM (
        SELECT CUST, PRODUCT, QTY, ROW_NUMBER() OVER(Partition BY PRODUCT ORDER BY PRODUCT) as Rank
        FROM Product) up
        PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt ORDER BY Rank.

        Please make a note – in my case all the columns are of varchar type.

        • @Vishal.

          I forgot, we have to specify Order by Clause in OVER PARTITION FUNCTION. This does not solves your problem.

          Okay, Let me ask this, What is the order you want to display data in output. Like order by Date time field ?

          ~ Peace.

  29. Hi Imran

    Actually i don’t want any sorting. I just want data without any sorting.

    Let me explain briefly

    I have table structure like
    Patient Id, Date, Temperature, Blood Pressure, Heart Rate etc as my columns.

    I want output like all the Date values will be my columns and rest of the columns should becomes my row.

    So first i have Unpivoted it which results me as all the columns become my rows with column name as “Name”, “Value” and “Date”.

    i.e.
    (Unpivoted Result Table)
    Name Value Date
    ———————————————————–
    Temperature 35 12/1/2010
    Blood Pressure 110 16/7/2010
    Heart Rate 70 12/1/2010
    Temperature 37 16/1/2010

    Then i have pivoted it with Date. It works fine. but it automatically sort by “Name” which i don’t want. I want the same sequence which i got in unpivoted table.

    i.e.
    (Pivoted Result Table) – Which i got by default order
    Name 12/1/2010 16/7/2010
    ———————————————————–
    Blood Pressure NULL 110
    Heart Rate 70 NULL
    Temperature 35 37

    My expected pivoted result.

    Name 12/1/2010 16/7/2010
    ———————————————————–
    Temperature 35 37
    Blood Pressure NULL 110
    Heart Rate 70 NULL

    I hope you are now cleared with my query.

    Thanks.

  30. Hi Pinal,
    I need your help…
    my table structure is in the below format

    PFAccountNo employee_name SalaryForTheMonth PF
    ————————————————– ———————-
    54544 KANCHAN 2010/jun/18 780
    5456 JAYANTHI 2010/jun/18 780
    54544 KANCHAN 2010/jul/18 720
    5456 JAYANTHI 2010/jul/18 680
    54544 KANCHAN 2010/aug/18 700
    5456 JAYANTHI 2010/aug/18 620

    and I need the Datas in

    PFAccountNo employee_name jun_PF July_PF Aug_PF
    ————————————————– ———————-
    54544 KANCHAN 780 720 700
    5456 JAYANTHI 780 680 620

  31. Hi,
    i have result set in this format

    Sorce YearMonth CountEntery
    A 2003-04 43
    B 2003-04 196
    A 2003-05 135
    B 2003-05 507

    i want to pivot it to

    Source 2003-04 2003-05
    A 43 135
    B 1964 5079

    can any one help me how to get this

  32. SELECT ‘Title’ AS Sort_By_Title,
    [Sales Representative], [Vice President, Sales], [Sales Manager], [Inside Sales Coordinator]
    FROM
    (SELECT FirstName,Title FROM dbo.Employees) AS SourceTable
    PIVOT
    (
    MAX(FirstName)
    FOR Title IN
    ([Sales Representative], [Vice President, Sales], [Sales Manager], [Inside Sales Coordinator])
    ) AS PivotTable;

    Incorrect syntax err. Can someone suggest?

  33. Data Before Pivot>>

    Product_Family Country_Code Day_Actual_Revenue MTD_Actual_Revenue YTD_Actual_Revenue MTD_Expected_Revenue YTD_Expected_Revenue MTD_Index_Revenue YTD_Index_Revenue Day_Actual_Units MTD_Actual_Units YTD_Actual_Units MTD_Expected_Units YTD_Expected_Units MTD_Index_Units YTD_Index_Units
    HAIR US 2970.000 551709.000 1199355.000 464062.500 1039500.000 119.000 115.000 6.000 1103.000 2392.000 937.500 2100.000 118.000 114.000
    ACNE US 245.000 32565.200 68877.000 56250.000 126000.000 58.000 55.000 1.000 127.000 277.000 250.000 560.000 51.000 49.000

    Data After Pivot >>>

    Type(Rev or Units) Label Hair_Avg Hair_Index Acne_Avg Acne_Index Total_Avg Total_Index
    Revenue Day 2970 245 3215
    Revenue MTD 22068.36 119 1302.61 58 23370.97 112.29
    Revenue YTD 21417.05 115 1229.95 55 22647 108.81
    Units Day 6 1
    Units MTD 44.12 118 5.08 51
    Units YTD 42.71 114 4.95 49

  34. Hello there,
    thank you for the explanation, it ‘been useful in my current project though I still have a concern;
    Iwas wondering if it’s possible to generate dynamically the fields contained in the pivot.

    A typical case would be to get sum of products based on a period, let’s say for instance between 12months as from current month;
    This way, the result shown would be constantly moving from month to month and would only display the last 12 ones.
    So far I only can display 12 fixed months from JAN to DEC with an additonal parameter forcing the user to change the current year.

  35. Hi,

    I wants to show cross matrix which will contain data like

    Monday Tuesday,….. Friday
    Client xyz xxx
    Subject testemail test
    owner ABC CCC

    Please let me know how it can be done?

  36. Hi all,

    I am having a table containing data in the below format in sql server.

    Person Day1 Day2          Day3         Day4 val
    Penchal        45 58 44 98 NULL
    Venkat          44 59 78 23 NULL
    Vijay              48 60 66 54 NULL

    Let me know the hw to make the above data as below.

    Person Day val
    Penchal Day1 45
    Penchal Day2 58
    Penchal Day3 44
    Penchal Day4 98
    Venkat Day1 44
    venkat Day2 59
    Venkat Day3 78
    venkat Day4 23
    .
    .
    .
    .
    .
    .
    Regards,
    U.Penchal Reddy.

  37. I had a hard time finding useful articles for a beginner like me, but this was an excellent article! Thanks so much

  38. Hi All,

    I created a table as shown below.By using ssis package DeptID 10 data should be transfered into seperate table ,DeptID 20 data should be transfered into seperate table…..but the condition is that when i entered DeptID 50,60……in the original table for each and every DeptID seperate tables should be created in the Database when i run the package.can anyone give clue for this pls……

    DeptID DeptName Location
    10 computers chennai
    10 computers Hyd
    20 electronics Delhi
    20 electronics Hyd
    20 electronics mumbai
    30 commerce CPT
    30 commerce Gunture
    30 commerce NRT
    30 commerce VNK
    40 maths BAD

    Thanks,
    U.Penchal Reddy.

  39. Hi, i have this code, how it changes to sql server 2000 ??

    TRANSFORM Count(dbo_CLIENTES.FECHA_CREACION) AS CuentaDeFECHA_CREACION
    SELECT dbo_CLIENTES.CLI_EMPRESA AS EMPRESA
    FROM dbo_C_CALZADO INNER JOIN dbo_CLIENTES ON dbo_C_CALZADO.ID_CLIENTES = dbo_CLIENTES.ID_CLIENTES
    WHERE (((dbo_CLIENTES.EXTRANJERO)=”TRUE”))
    GROUP BY dbo_CLIENTES.CLI_EMPRESA
    ORDER BY dbo_CLIENTES.CLI_EMPRESA
    PIVOT Year(DBO_CLIENTES.FECHA_CREACION);

    Thanks a lot !!

  40. in your example, while doing unpivot, do we need to add pivot then unpivot………

    SELECT CUST, PRODUCT, QTY
    FROM
    (
    SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
    FROM (
    SELECT CUST, PRODUCT, QTY
    FROM Product) up
    PIVOT
    ( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
    UNPIVOT
    (QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
    ) AS Unpvt
    GO

    ——————————————-

    here, you added pivot and unpivot…….

    is there nay need of pivot

  41. Hi, i am currently working on a project where I have a column called ‘Month’ which has months from Jan to Dec and correspondingly have a column called bill amount. I have to to display the bill amount for every month individually and then the sum of those . I am able to get the sum using PIVOT. But I am not able to display those values. Is there any way that I can display the moth and then the sum? Thank you.

  42. hi All,
    I have a requirement where the table values look like below
    Parent ID is also a document ID. For each parent ID there may be one or more child document associated.
    Document ID Document# doc Name Doc start date end date parent ID
    101 0001 Aaa 1/1/90 1/2/90 334
    102 0003 Sss 3/3/99 3/4/99 334
    222 4566 Ano 5/5/05 6/6/06 null
    334 9879 Parent 1/1/89 1/1/89 null

    Now in my report, I need data as below
    Document ID1 Doc start date1 end date1 DocumentID2 Doc start date2 end date2
    101 1/1/90 1/2/90 102 3/3/99 3/4/99

    please help

    thanks
    preethi

  43. Good site..
    My query is…

    i have 3 columns
    item, remarks, remarksby, date
    a, ‘good’, ‘sonu’ , day1
    a, ‘bad’, monu’ , day2
    d, ‘too good’, ‘raju’ , day1
    d, ‘ok’, ‘golu’ , day2

    i have to show like this…

    item , remarks1, remakrsby1, remarks2, remarksby2, remakrs3,remarksby3
    a , good, sonu, bad, monu
    d, too good , raju, ok , golu

    please let me know how to do this … ???
    All thanks in advance

    • declare @remarks table (item varchar, remarks varchar(20), remarksby varchar(20) ,dat varchar(20))

      insert into @remarks
      select ‘a’, ‘good’, ‘sonu’ , ‘day1′ union all
      select ‘a’, ‘bad’, ‘monu’ , ‘day2′ union all
      select ‘d’, ‘too good’, ‘raju’ , ‘day1′ union all
      select ‘d’, ‘ok’, ‘golu’, ‘day2′

      –select * from @remarks

      SELECT
      item,
      max(case when id=1 then remarks
      end) as remarks1 ,
      max(case when id=2 then remarks
      end) as remarks2,
      max(case when id=1 then remarksby
      end) as remarksby2,
      max(case when id=2 then remarksby
      end) as remarksby2
      FROM (
      SELECT
      row_number()over (partition by item order by item)id,
      item,
      remarks,
      remarksby
      FROM @remarks
      ) up

      group by
      item

  44. It was a nice article Dave,Thanks.For those looking for alternative solution below is the solution:

    it can also be done using case statement

    select Product,
    max(case cust when ‘fred’ then Qty end) as ‘FRED’,
    max(case cust when ‘kate’ then Qty end) as ‘KATE’
    from Product
    group by Product

  45. Is there any way to make something like pivot without using aggregate functions? I need to see the details, not the sum. How can i do something like a crosstable in SQL Server?

  46. holas necesito su ayuda kisiera unir dos tablas o mas y que los campos de las tablas no me salgan en forma horizontal , ejemplo :

    select tabla_prueba .id,nombre,apellido,tabla2.id,pieza,grupo from tabla_prueba
    INNER JOIN tabla2 ON tabla_prueba.id=tabla2.id

    este es el resultado de la consulta

    id,nombre,id,nombre
    1 asas 3 ad
    2 zxzx 4 ads

    quisiera ke salgan asi

    id,nombre
    1 asas
    2 zxzx
    id,nombre
    3 ad
    4 ads

  47. I have a requirement in which after Pivot, I don’t want the aggregated value, instead I need ‘Y’ and for Null values it should be ‘N’. How can I achieve this? Please help me.

  48. What if I have only one column in mytable and i want the values to be displayed horizontally??
    eg. table ‘ONE”
    column
    data
    1
    2
    3
    I want to display as :
    data 1 2 3
    ??

  49. Pingback: SQL SERVER – Weekly Series – Memory Lane – #032 | Journey to SQL Authority with Pinal Dave

  50. I need help. I am using sql 2005 and I am trying to do the following query and output to a text fle with fixed-length format in stored procedure once per week:
    Before the printing list can be generated, SQL should select each household (using family number as an indicator) that the member/s of the same household need to have a status of ’40’ in order for that member to be included in the printing list; other valid statuses are ’73’ or ’76’ but not the status of ‘R’ or anything above a status of ’90’. Please note the ‘status’ column is a string type variable. (It is not use in computation).

    Database name = Scores; Table name = BMA; using SQL 2005; in the table-design of BMA, I have the following — (column names, data types, allow nulls). There are two primary keys and cannot contain a ‘null’ value.

    column names data types allow nulls
    (primary key) FamilyNumber varchar(12) no null
    (primary key) FamilyMemNum varchar(8) no null
    Status varchar(3) null
    ActionNeeded varchar(1) null
    SendToPrint varchar(1) null

    Partial row records are as follow:

    FamilyNumber FamilyMemNum Status ActionNeeded SendToPrint
    11011011011A A1345678 40 P P ***This household has 3 members and member# (K3345678)
    11011011011A G2345678 73 P prevented a mailing due to status=’02’ instead of a ’40’ or ’73’
    11011011011A K3345678 02 P or ’76’. SendToPrint is always ‘P’ for the whole family when
    there is at least one family member currently with a ‘P’ in
    ActionNeeded, or a family member who had status=’40’ previously
    & is now a ’73’ or ’76’. This whole family will be included in the
    printing list when the family member (K3345678) status changes to
    a ’40/73/76′.
    22022022022A B2345678 40 P P ***This household only has 1 member, and with a status = ’40’, print
    this.
    33033033033B A1678901 76 P ***This household has 3 members, and all members has status of
    33033033033B B1943250 40 P P either 73, 76 or 40, print this. But only include (B1943250) and
    33033033033B K2583690 40 P P (K2583690) in the print list, since they have ‘P’ for ActionNeeded &
    SendToPrint.
    44044044044G L6521234 40 P P ***This household has 2 members, and both has status =’40’, and ‘P’
    44044044044G B1345678 40 P P (Print) in both ActionNeeded and SendToPrint, therefore print this.
    55055055055R V2134567 91 P P ***This should be excluded from the print list even though it has
    ‘P’ in both ActionNeeded and SendToPrint columns, because the
    family do not have a status of ’40’ or ’73’ or ’76’.
    66066066066E Y7531908 73 P ***This family should be excluded from the print list although both
    66066066066E C1253602 76 P has ‘valid’ print status of ’40/73/76′, but since none of their
    status=’40’, do not include this family (66066066066E) in this print
    list.

    *** Each set or groupings of FamilyNumber is similar to an apartment having ‘The Smith’s’ household as an example (ie: 11011011011A), and FamilyMemNum is similar to each individual children and spouse living in that apartment, each person is identified with a FamilyMemNum (ie: A1345678, G2345678, K3345678), to be included in the printing list of the week, each FamilyMemNum should have a status of either ’40’ or ’73’ or ’76’, otherwise nobody is included on the print list of the week, (ie: K3345678) has a status of ’02’, that disqualifies the whole family (11011011011A) from making to the printing list of the week, until that family member’s status changed to one of the ‘valid’ printing status of ’40’ or ’73’ or ’76’. Once (K3345678) has a valid status, say a ’76’ which makes the whole family ‘valid’ to be included in the printing list, but only individuals with a status of ’40’ in a ‘valid’ family will get a mail. Those with ‘valid’ status of ’73’ and ’76’ need not be included in the mailing list.

    (output to a flat file or text file in fixed-length format from stored procedure, no space or comma separating the two columns in the output file):
    like so — FamilyNumberFamilyMemNum

    Printing list of the week:
    22022022022AB2345678
    33033033033BB1943250
    33033033033BK2583690
    44044044044GL6521234
    44044044044GB1345678

    *** The rest are not included on the print list due to one of the family members do not have a ‘valid’ print status of ’40’ or ’73’ or ’76’ making the whole family ‘invalid’ for the print list even if there is one member who may have a valid status, the invalid family member supercedes the valid family member. Another situation would be the whole family or a part of it has a valid status of ’73’ or ’76’ and these people may have had a status of ’40’ previously, therefore need no further mailing.

    Thank you in advance for helping me solving this difficult SQL query.
    — Larry

  51. I have a common problem on SQl side. I am providing details and data and also required output as below:
    there are two table
    1) Table1 has data and column:

    PortfolioID |Project | Description | Jan |Feb | March | April |May |Jun|July|Aug|Sept|Oct|Nov|Dec
    1 | P1 | Test1 |-62.5 |-62.5|-62.5|-62.5|-62.5|-62.5|-62.5|-62.5|-62.5|-62.5|-62.5|-62.5
    1|Project|Des|2012-1|2012-2|2012-3|2012-4|2012-5|2012-6|2012-7|2012-8|2012-9|2012-10|2012-11|2012-12

    2) Table 2:

    PortfolioID |Project | Description | Jan |Feb | March | April |May |Jun|July|Aug|Sept|Oct|Nov|Dec
    1 | P1 | Test1 |-6 |-6|-5|2.5|.5|-6|5|-6|-6|-6|-6|-62.5
    1|Project|Des|2012-1|2012-2|2012-3|2012-4|2012-5|2012-6|2012-7|2012-8|2012-9|2012-10|2012-11|2012-12

    Output:
    PortfolioID|Project|Des|Date|Table1Data|Tabl2Data
    1 P1 Test1 2012-1 0 -6
    1 P1 Test1 2012-2 0 -6
    1 P1 Test1 2012-3 0 -6
    1 P1 Test1 2012-4 0 -6
    1 P1 Test1 2012-5 62.5 -6
    1 P1 Test1 2012-6 0 -6
    1 P1 Test1 2012-7 0 -6
    1 P1 Test1 2012-8 0 -6
    1 P1 Test1 2012-9 0 -6
    1 P1 Test1 2012-10 0 -6
    1 P1 Test1 2012-11 0 -6
    1 P1 Test1 2012-12 0 -6

    Please help me and Thanks in advance

  52. Here’s a different approach using CTE:

    WITH pivotdata as (
    select
    Product,
    cust,
    qty
    from product
    )
    SELECT Product,[FRED],[KATE]
    from pivotdata
    pivot(SUM(qty) for cust in ([FRED],[KATE])) as p;

  53. I want to create a Pivot Table using a Table Variable. Following is my script which gives error
    Msg 1087, Level 15, State 2, Line 8
    Must declare the table variable “@DateTable”

    This means the table variable is not accessible to Execute statement
    ==============================
    Declare @DateTable Table
    (MasterDate date, DoM int, Mth int, TotalPaxInLocation int)
    Declare @GivenDate date
    Declare @Location nvarchar(6)
    Declare @StartDate Date
    Declare @EndDate Date
    Declare @TotalPax int

    Set @StartDate = ‘2014-01-01′
    Set @EndDate = ‘2014-12-31′
    Set @Location = ‘MADINA’
    Set @GivenDate = @StartDate

    While @GivenDate <= @EndDate
    BEGIN
    Insert into @DateTable
    (MasterDate, Dom, Mth)
    Select @GivenDate, datepart(d,@givendate), datepart(M, @GivenDate)

    Select
    @TotalPax = isnull(sum(dbo.TotalPax(RM.RegID)),0)
    from RegistrationMaster RM
    Where
    Case
    When @GivenDate Between RM.ArrivalDate and RM.DepDate
    Then Case When @GivenDate Between RM.ExpMadinaDate and RM.ExpMadinaReturn then 'MADINA' else 'MAKKAH' end
    else
    Case When @GivenDate < RM.ArrivalDate then 'NOT ARRIVED' else 'DEPARTURE' end
    END = @Location

    Update @DateTable
    Set TotalPaxInLocation = @TotalPax
    Where MasterDate = @GivenDate

    Set @GivenDate = DATEADD(D,1,@GivenDate)
    END

    Select DoM, Mth, TotalPaxInLocation from @DateTable
    order by MasterDate

    DECLARE @PivotColumnHeaders VARCHAR(MAX)
    SELECT @PivotColumnHeaders =
    COALESCE(
    @PivotColumnHeaders + ',[' + cast(A.DoM as varchar) + ']',
    '[' + cast(A.DoM as varchar)+ ']'
    )
    FROM
    (
    select
    distinct DoM
    from @DateTable DT
    ) A

    DECLARE @PivotTableSQL NVARCHAR(MAX)

    SET @PivotTableSQL = '
    SELECT *
    FROM (
    Select * from
    (
    Select
    DoM, Mth, TotalPaxInLocation
    From @DateTable)

    AS PivotData
    PIVOT (
    Sum(TotalPaxInLocation)
    FOR DoM IN (
    ' + @PivotColumnHeaders + ' ) )

    AS PivotTable ) A

    '

    EXECUTE(@PivotTableSQL)

  54. Hi

    I have database in structure like below

    Newspaper State1 state2 state3 state4
    ABC 1000 5684 5687 246
    DEF 879 2547 2578 2357

    But I need to the structure like below

    How can i change this

    Newspaper State copies
    ABC State1 1000
    ABC State2 5684
    ABC State3 5687
    ABC State4 246
    DEF State1 879
    DEF State2 2547
    DEF State3 2578
    DEF State4 2357

    Pls help me

    Maideen

  55. table return this type record as per table structure

    VendorId MON TUE WED THU FRI SAT SUN
    ———- ———– ———– ———– ———– ———– ———– ———–
    SPIKE 900 200 500 300 300 100 400

    and i want this type

    VendorId Day IncomeAmount
    ———- ———- ————
    SPIKE MON 900
    SPIKE TUE 200
    SPIKE WED 500
    SPIKE THU 500
    SPIKE FRI 200
    SPIKE SAT 900

  56. Hello mr. Pinal have some sample log as follows:

    Cod. name description quantity
    ——————————————————
    001 PAPER-A good 50
    002 PAPER-B medium 40
    003 PAPER-C 30
    004 PAPER-D good 50
    :
    :
    and records of the “description” column I want to become columnar but as I do with the vacuum?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s