SQL SERVER – Example of PIVOT UNPIVOT Cross Tab Query in Different SQL Server Versions

Transforming rows to columns (PIVOT/CROSS TAB) and columns to rows (UNPIVOT) may be one of the common requirements that all of us must have seen several times in our programming life. SQL Server 2005 introduced two new operators: PIVOT and UNPIVOT that made writing cross-tab queries easier.

My friend and SQL Server MVP Jacob Sebastian has posted an example that transform rows to columns using PIVOT operator. The reverse operation of PIVOT is UNPIVOT. PIVOT operator is available only in SQL Server 2005/2008. It does not exists in SQL Server 2000. Developers who are still using SQL Server 2000 should upgrade to SQL Server 2005 or SQL Server 2008 (recommended).

While discussion this issue with Jacob another day he wrote following posts so fast it was very impressive.

TSQL Lab 15 – Another PIVOT Query Example
TSQL Lab 16 – Generating CROSS TAB results in SQL Server 2000
TSQL Lab 17 – An example using UNPIVOT (Transforming Columns to Rows)

Articles written by me previously.

SQL SERVER – PIVOT and UNPIVOT Table Examples
SQL SERVER – PIVOT Table Example
SQL SERVER – UNPIVOT Table Example

Reference : Pinal Dave (http://blog.SQLAuthority.com)

14 thoughts on “SQL SERVER – Example of PIVOT UNPIVOT Cross Tab Query in Different SQL Server Versions

  1. my problem is:

    1 table store IDNo, ItemName,itemvalue,itemamount.
    other table store IDNo,tax1,tax2,party1,party2,party3,

    I went to show like this

    party1 party2 party3
    itemname 200 210 220
    tax1 10 11 12
    tax2 1 3 4

    but I try not do liks this .

    if any solution please tell me.

    dinesh

    Like

    • I have this type of Data:

      Here Investigation Date is Combo-Box, where date can be selected:
      ——————————————————————————————
      Investigation Date 21/09/2011
      ——————————————————————————————
      Test Name Result Unit Range Comments
      ——————————————————————————————
      Blood Test 1.5mg mg 1.0-2.5 —–
      Blood Test 2.0mg mg 1.0-2.5 —–
      Urine Test 7.5mg/dl mg/dl 4.0-15.5 —–
      ——————————————————————————————
      See Above 2 Blood test are inserted, While first test is done on 21/09/2011 & second on 25/09/2011.
      And Urine Test Done on 25/09/2011.

      While i had use this access query in VB.Net coding Directly which runs successfully.

      ========================================================
      Access Query:
      TRANSFORM First(dtInvestigation.InvValue & ‘ ‘& InvUnit) AS FirstOfInvId SELECT mstInvestigation.InvstName FROM dtInvestigation INNER JOIN mstInvestigation ON dtInvestigation.InvId = mstInvestigation.InvstId Where dtInvestigation.InvDate Is Not Null and Patid=1 GROUP BY mstInvestigation.InvstName PIVOT (dtInvestigation.InvDate) as pvt;
      ========================================================

      *** Now i want the data like this ***

      ——————————————————————————————
      Test Name 21/09/2011 25/09/2011
      ——————————————————————————————
      Blood Test 1.5mg 2.0mg
      Urine Test 7.5mg/dl
      ——————————————————————————————

      Please make it fast and send me the reply as soon as possible
      You can reply me here :- [email removed]

      Regards,
      Amit Uttekar
      [email removed]

      Like

  2. i have one table called datamart…in which 1 column named quantity exists… quantity has a data such as 2.00,3.00 & etc…
    when i select a data through query…
    i.e. select quantity from datamart
    it shows me a data like…
    quantity
    2.00
    3.00
    and so on..
    but i want to have a data in following format…
    quantity 2.00 3.00 and so on..
    please help me on this soon…
    Amisha

    Like

  3. SQL Problem
    Assume we have loaded a flat file with patient diagnosis data into a table called “Data”. The table structure is:

    Create table Data (
    Firstname varchar(50),
    Lastname varchar(50),
    Date_of_birth datetime,
    Medical_record_number varchar(20),
    Diagnosis_date datetime,
    Diagnosis_code varchar(20))

    The data in the flat file looks like this:

    ‘jane’,’jones’,’2/2/2001′,’MRN-11111′,’3/3/2009′,’diabetes’
    ‘jane’,’jones’,’2/2/2001′,’MRN-11111′,’1/3/2009′,’asthma’
    ‘jane’,’jones’,’5/5/1975′,’MRN-88888′,’2/17/2009′,’flu’
    ‘tom’,’smith’,’4/12/2002′,’MRN-22222′,’3/3/2009′,’diabetes’
    ‘tom’,’smith’,’4/12/2002′,’MRN-33333′,’1/3/2009′,’asthma’
    ‘tom’,’smith’,’4/12/2002′,’MRN-33333′,’2/7/2009′,’asthma’
    ‘jack’,’thomas’,’8/10/1991′,’MRN-44444′,’3/7/2009′,’asthma’

    You can assume that no two patients have the same firstname, lastname, and date of birth combination.

    The problem is this: Tom Smith has 2 different medical record numbers. Write a query that would always show all the patients who are like Tom Smith – patients with more than one medical record number.

    This problem has many solutions, but if you know SQL, you should be able to find one that uses a single query with no subqueries.

    Like

  4. select * from (SELECT DISTINCT(T.VCH_YEAR),CONVERT(VARCHAR,T.NUM_QUANTITY)+T.VCH_UNIT AS QUANTITY ,CONVERT(VARCHAR,M.DTM_PLAN_APPROVAL,106) AS DTM_PLAN_APPROVAL FROM M_MIS_MININGPLAN_ML M,T_MIS_MININGPLAN_ML T
    WHERE M.INT_MP_ID=T.INT_MP_ID AND M.BIT_DELETED_FLAG=0 AND T.BIT_DELETED_FLAG=0 and m.INT_CIRCLE_ID=7 AND T.INT_MINERAL_ID=13 AND M.INT_LESSEE_ID=271
    GROUP BY VCH_YEAR,CONVERT(VARCHAR,T.NUM_QUANTITY)+T.VCH_UNIT,DTM_PLAN_APPROVAL )
    gettable PIVOT (Max(DTM_PLAN_APPROVAL) FOR QUANTITY IN
    (Can i write theselect statement here.))AS riii

    Like

  5. Hi and thanks for all the great information! I have a scenario I need help with and I am hoping you can help me out.

    I have a table that looks like this;

    EMP NAME | STAT NAME | STAT | WEEK
    “JACK” | “NUMBER OF VISITS” | 12 | 1
    “JACK” | “NUMBER OF VISITS” | 22 | 2
    “SUSAN” | “NUMBER OF CALLS HANDLED” | 125 | 1
    “SUSAN” | “NUMBER OF CALLS HANDLED” | 105 | 2

    My problem is that I need to return a single row for each “Stat Name” per employee so that my data set would look like this;

    EMP NAME | STAT NAME | WEEK 1 | WEEK 2
    “JACK” | “NUMBER OF VISITS” | 12 | 22
    “SUSAN” | “NUMBER OF CALLS HANDLED” | 125 | 105

    I am kinda lost on what the right thing to do is to get this type of result. Any suggestions?

    Thanks in advanced!

    Like

  6. I Have One Query

    I want to Dispaly Out Put as like

    agentId | Mon | tue | wed | thur | fri | sat | sun
    1 5 pm 5 pm 5 pm 5 pm 5 pm 5 pm 5 pm

    ======================================
    My Table like this

    CREATE TABLE [dbo].[CRMScheduleMaster](
    [ScheduleID] [int] IDENTITY(1,1) NOT NULL,
    [AgentID] [int] NOT NULL,
    [WeekID] [int] NULL,
    [StartTime] [varchar](50) NULL,
    [EndTime] [varchar](50) NULL,
    CONSTRAINT [PK_ScheduleMaster] PRIMARY KEY CLUSTERED
    (
    [ScheduleID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    ================

    So pls Give the Solution.

    Mu\y Id is : [email removed]

    Like

  7. i reallly want your help in makin a cross tab query … here is the

    scnerio . plzzz help me ….

    there are many tables .

    tb

    datatype

    sno autogenerate , date varchar(16) , time varchar(10) ,rhrs int,

    sno date time rhr_s

    1 26/01/2012 07.00 2801

    1 26/01/2012 08.00 2802

    1 27/01/2012 09.00 2803

    1 27/01/2012 10.00 2804

    tb1

    datatype

    sno autogenerate , date varchar(16) , time varchar(10) ,rhr_s1 int,

    sno date time rhr_s

    1 26/01/2012 07.00 2801

    2 26/01/2012 08.00 2802

    3 26/01/2012 09.00 2803

    4 26/01/2012 10.00 2804

    5 27/01/2012 07.00 2811

    6 27/01/2012 08.00 2812

    7 28/01/2012 09.00 2813

    8 28/01/2012 10.00 2814

    tb2

    datatype

    sno autogenerate , date varchar(16) , time varchar(10) ,rhr_s2 int,

    sno date time rhr_s2

    1 26/01/2012 07.00 2811

    2 26/01/2012 08.00 2812

    3 27/01/2012 09.00 2813

    4 27/01/2012 10.00 2814

    i want a crross tab in this way in date format randomly no fixed date

    **cols ————–26/01/2012—————————-27/01/2012

    —————— 28/01/2012**
    \
    \
    \
    \
    rhr_s–(max-min where date = 26/01/2012) (max-min where date =

    27/01/2012) (same formula)
    \
    \
    \
    \

    rhr_s1 as above as above

    as above
    \
    \
    \
    \

    rhr_s2 as above as above

    as above
    please help me in making this type of report …..
    i reallly i trouble

    Like

  8. hi sir plz help for this problem

    In my table the values are stored as
    empid ename date status
    ————————————————
    101 abc 01/02/2013 present
    101 abc 02/02/2013 present
    101 abc 03/02/2013 absent
    102 xyz 03/02/2013 present
    and so onnnn
    101 abc 28/02/2013 present
    102 xyz 28/02/2013 present

    here i mention only one employe record.There is n number employes there my problem is
    how to show table like this

    empid ename 1 2 3 4 5 6 …….28
    ————————————————————————
    101 abc present present absent…………. presnt
    102 xyz present present present………..present

    here 1 2 3 4 represent dates of the month.

    plese kindly provide me the query to get like this.

    thanks.

    Like

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