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)
79 Comments. Leave new
what is the query of converting column fields into row fields by using pivot table?
great , that done my job easy
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
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.
Good Post
Good Post….
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
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…
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
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
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,
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?
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
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
You need dynamic PIVOT. Refer this post
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
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
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
I need help
How can access EXEC StoredProcedure1 result set in StoredProceure2
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.
Nice article Merlin