SQL SERVER – PIVOT Table Example

This is quite a popular question and I have never written about this on my blog. A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.

USE AdventureWorks
GO
SELECT [CA], [AZ], [TX]
FROM
(
SELECT sp.StateProvinceCode
FROM Person.Address a
INNER JOIN Person.StateProvince sp
ON a.StateProvinceID = sp.StateProvinceID
) p
PIVOT
(
COUNT (StateProvinceCode)
FOR StateProvinceCode
IN ([CA], [AZ], [TX])
) AS pvt;

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

I really think this is a 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 the original data back

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.
Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – 2005 – Twelve Tips For Optimizing Sql Server 2005 Query Performance
Next Post
SQL SERVER – SQL SERVER – UDF – Get the Day of the Week Function – Part 2

Related Posts

79 Comments. Leave new

  • what is the query of converting column fields into row fields by using pivot table?

    Reply
  • Prithvi Nath Pandey
    December 22, 2011 7:54 pm

    great , that done my job easy

    Reply
  • hi pinal,

    I have doubt can you please help me..

    Required output in this format.

    empid GS NS MS AS total
    1001 3 2 1 2 8
    1002 1 4 3 0 8

    Below is the table structure
    empid Shift Date
    1001 GS 01-Jan-2012
    1001 GS 02-Jan-2012
    1001 GS 03-Jan-2012
    1001 NS 04-Jan-2012
    1001 NS 05-Jan-2012
    1001 MS 06-Jan-2012
    1001 AS 07-Jan-2012
    1001 AS 07-Jan-2012
    1002 GS 01-Jan-2012
    1002 NS 02-Jan-2012
    …..
    …..
    ….
    1002 AS 07-Jan-2012
    etc…

    Please help me to solve this issue… It’s really urgent
    if you can reply early I’ll really appreciate

    Reply
  • Hi

    I have used Pivot in my Stored procedure.

    Like while I was executing, there was a bit server blockings . Is it really due to that. And as per performance point of which is better While Looping is that better or PIVOT is that better.

    Reply
  • Vishal Misquitta
    February 21, 2012 8:00 pm

    Good Post

    Reply
  • Vishal Misquitta
    February 21, 2012 8:06 pm

    Good Post….

    Reply
  • Hi

    I am looking for a dynamic query which will populate the multiple records from a column into a row with a unique article id. e.g.

    FROM

    Art_Id Product
    101308 BD258
    101309 BD259
    101301 BD214
    101308 BD345
    101309 MD852

    …. more than 2million records

    TO

    ART_ID COL1 COL2 …………. n column
    101308 BD258 BD345
    101301 BD214
    101309 BD259 MD852

    WHEREVER I HAVE SEEN THE HELP BLOG ON IT IT GOT CALCULATION INVOLVED BUT IN MY CASE I DO NOT HAVE ANY CALC. JUST WANT TO TRANSPOSE MY TABLE.

    THANKS IN ADVANCE FOR HELP

    Reply
  • sir i want a table like this. my base table is empid,cellphno,officeno,residenceno.but i want data like this manner
    empid,number,type. in number column it display all the three no of empid 1 then it display empid 2.in type if it is cellph it show ‘c’ , if officeno it shows ‘o’ like this.
    please help me…

    Reply
  • Ryu-Jin Kurozuka
    April 16, 2012 6:04 pm

    How To Create a Pivot Table for this one

    1 1 1 500
    2 1 2 300
    3 2 1 300
    4 3 2 400
    5 3 1 8000

    Reply
  • hourname sublistTitle Day
    1stHour Social Monday
    2nd Hour Maths Monday
    1stHour Telugu TuesDay
    3rd Hour Social WedDay
    1stHour Social FriDay
    3rd Hour Computers FriDay

    query:

    select ch.hourname,dta.sublistTitle,(select d.DayName from DayTable d where d.dayid=t.dayid) as Day from timetable t,classhours ch,(select c.cstuid as cstuid,sl.sublistTitle as sublistTitle from subjectslist sl,subjects_school ss,cst c where sl.sublistid = ss.sublistid and ss.schoolsubuid=c.schoolsubuid and c.suid = 1030) dta where t.houruID = ch.classHouuID and dta.cstuid = t.cstuid and t.classrumid=1004

    I need to get

    Day isthour 2nd hour 3rd hour 4th hour
    Monday Social Maths Null NUll

    Tuseday Telugu Null Null Null

    Wed Null Null Social NUll

    Thursaday NUll Null NUll Null

    Friday Social Null Computers NUll

    Please its Really very important and very urgent so kindly help me as esarly as possible today

    Thaking you

    jyothi

    Reply
  • I have these tables.

    table1:

    IDNumber LName Fname Book Section Title
    11355 Smith John CCC 2145 General Idea
    11311 Doe John BBB 2446 General Health
    11433 Doe John DDD 2554 College Politcs
    11377 Brown Ben FFF 2610 How to survive
    11388 Cam Rachel SSS 1274 Hapiness
    11344 Caml Rachel HHH 2845 Solution
    AND

    table 2:

    IDNum Q01 Q02 Q03 Q04 Q05 Q06 Q07
    11355 5 5 5 5 5 5 5
    11355 5 5 5 5 5 5 5
    11355 4 5 4 4 5 4 4
    11322 4 4 4 4 5 4 5
    11322 5 5 5 5 5 5 5
    11300 5 5 5 5 5 5 5
    11300 3 4 3 5 5 4 3
    11300 5 5 5 5 5 5 5
    11300 5 5 5 5 5 5 5
    11300 4 4 4 4 5 4 4
    11311 3 5 4 4 5 5 5
    11311 5 5 5 5 5 5 5
    11377 3 2 3 5 5 5 4
    11377 5 5 5 5 5 5 5

    I need to have a result:

    table 3:

    IDNum QID Lname Room Section Title
    11355 Q1 Smith CCC 2145 General Idea
    11355 Q2 Smith CCC 2145 General Idea
    11355 Q3 Smith CCC 2145 General Idea
    11355 Q4 Smith CCC 2145 General Idea
    11355 Q5 Smith CCC 2145 General Idea
    11355 Q6 Smith CCC 2145 General Idea
    11355 Q7 Smith CCC 2145 General Idea
    11322 Q1 Doe BBB 2246 General Health

    Thank you,

    Reply
  • Suppose…I have the following rows in a table MEMBERSHIP_NO, CLASS, LASTNAME, FIRSTNAME, MiddleName, HOME_ST, HOME_CITY, HOME_PROV_STATE, HOME_CTRY, HOME_PC, HOME_PHONE, POSITION with their values. I want to use Pivot on these columns but these rows might not be present for each of the MemberIDs. Some columns are missing for some members depending on the data availability. How can I handle the missing data rows (columns) in my Pivot Report?

    Reply
  • Hi ,

    Its really great post. I think i can solve my current problem with PIVOT.

    Can anyone please help how to do this with PIVOT. I also tried but didnt get the exact out put what i want.

    Available Data

    EmpCode PayCode Value
    ————————————————
    E001 HRA 2000
    E001 BASIC 45000
    E001 TA 5000

    The output format how I want

    EMPCODE HRA BASIC TA
    ——————————————————————
    E001 2000 45000 5000

    Thanks in advance.

    Plahanov

    Reply
  • bubai banerjee
    August 15, 2012 5:50 pm

    Hi Pinal Sir,
    I have doubt can you please help me..I am very new in s/w DEvelopment. below i am describing.

    Original Table Structure.

    ID Date TimeLogged UserName
    1 10/8/2012 5.5 Bubai
    2 11/8/2012 2.3 Bubai
    3 10/8/2012 3.3 Bhanu
    4 11/8/2012 7.3 Bhanu

    I want result like below. User Name should be dynamic.May be lot of users.User name will come from Database table. Please give me broad description as I am very new in the system,for my understanding.

    Date Bubai Bhanu Total
    10/8/2012 5.3 3 8.3
    11/8/2012 2.3 7.3 10
    Total 8 10.3 18.3

    Reply
  • I having table Employee and Production tables.

    To show reports for target and achieved Value for the every month for an employee.

    below resulted table example:

    Name Date Target Value
    Admin 8/17/2012 0 8
    Admin 8/18/2012 0 6
    Admin 8/22/2012 0 1
    Admin 8/23/2012 0 11
    Admin 8/24/2012 0 3
    Admin 8/26/2012 0 2
    Babu 8/17/2012 500 376
    Babu 8/18/2012 500 313
    Babu 8/21/2012 500 172
    Babu 8/22/2012 500 305

    Pivot

    Name 1T 12T … Nt 1V 2V Nv
    Admin 0 0 0 0 8 6 0
    Babu 500 500 500 0 376 313 0
    .
    .

    with regards

    Merlin.P

    Reply
  • Hi i want to ask something about my work

    select * from
    (Select m.Brand,u.Color,Count(Unit)as [Total unit] from Brands m inner join Products u on m.BrandID=u.ProductID
    Group by m.Brand ,u.Color with cube)as Pvt
    PIVOT
    (
    Sum([total unit])
    For Color
    IN
    (
    [Black],[White],[Gray],[Silver],[Orange],[Dark Blue]
    )
    )AS Pivot_Table

    I want to show total Brand and Total color but this code just calculate each color and count every color per brand

    How can i get Total Brand and Total Color ????

    Please help me

    Reply
  • I have Table 2
    Table A
    ShowId,ShowName,
    1,Show1
    2,Show2
    :
    :
    :
    End Not know

    DanceId,DanceNumber,DanceName
    1,1,1
    2,1,2
    3,2,1
    :
    :
    :
    End Not Know

    I am looking for result like

    Row 1:Show1,Show2,Show3………………….
    Row 2: DaneNumber,DanceNume……
    Row 3: DaneNumber,DanceNume……
    :
    :
    How can we get this

    Reply
  • I need help
    How can access EXEC StoredProcedure1 result set in StoredProceure2

    Reply
  • 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.
    in this above case empid is also varchar

    thanks.

    Reply
  • Amit Obviously
    April 19, 2013 2:47 pm

    Nice article Merlin

    Reply

Leave a Reply Cancel reply

Exit mobile version