Pivot Unpivot data
Pivot example in sql server
hi,
first we will see what is pivot and unpivot,uses in sql server
first we will see what is pivot and unpivot,uses in sql server
Q: What is Pivot?
ans: A Pivot or pivoting data means get row data to column.
Systax of pivot query
select * from tableName pivot(agreegateFunction(field want to pivot) for column_Name in (row_data1,row_data2 so on) )
ans: A Pivot or pivoting data means get row data to column.
Systax of pivot query
select * from tableName pivot(agreegateFunction(field want to pivot) for column_Name in (row_data1,row_data2 so on) )
******** I can explain with example***********
for your better Understanding create a following tables
create table color (comid int, id int,Names varchar(50))
create table Quantity ( Qid int, id int,Quantity int)
Insert data in both tables
insert into color (comid,id,Names) values (1,1,''black'')
insert into color (comid,id,Names) values (1,2,''red'')
insert into color (comid,id,Names) values (2,3,''wt'')
insert into color (comid,id,Names) values (2,4,''re'')
insert into color (comid,id,Names) values (2,5,''Grey'')
insert into Quantity (Qid,id,Quantity ) values (1,1,20)
insert into Quantity (Qid,id,Quantity ) values (2,1,20)
insert into Quantity (Qid,id,Quantity ) values (3,2,25)
insert into Quantity (Qid,id,Quantity ) values (4,2,25)
insert into Quantity (Qid,id,Quantity ) values (5,3,30)
insert into Quantity (Qid,id,Quantity ) values (6,3,30)
insert into Quantity (Qid,id,Quantity ) values (7,4,35)
insert into Quantity (Qid,id,Quantity ) values (8,4,35)
insert into Quantity (Qid,id,Quantity ) values (9,5,3)
insert into Quantity (Qid,id,Quantity ) values (9,5,3)
select * from ( select comname,Names,Quantity from color a inner join company c on c.comid=a.comid inner join Quantity b on a.id=b.id ) DataTable PIVOT ( SUM(Quantity) FOR Names IN ( black,red,Grey,wt,re ) ) PivotTable
Q: What is UnPivot?
ans: A UnPivot is vice versa of pivot it means it is used when we want to get
Colunm data to rows
ans: A UnPivot is vice versa of pivot it means it is used when we want to get
Colunm data to rows
Q: What is UnPivot?
ans: A UnPivot is vice versa of pivot it means it is used when we want to get
Colunm data to rows
ans: A UnPivot is vice versa of pivot it means it is used when we want to get
Colunm data to rows
syntax of Unpivot data ::
select * from ( select * from comdetail ) DataTable UNPIVOT (Quantity FOR Names IN (black,red,Grey,wt,re) ) AS Unpvt
select * from ( select * from comdetail ) DataTable UNPIVOT (Quantity FOR Names IN (black,red,Grey,wt,re) ) AS Unpvt
Good example
ReplyDeleteit is very much helpful for me....
ReplyDeleteThanks
a nice example sir ,Can you please send me link of every new post of your blog
ReplyDeleteif you want to get my posts by mail then ,add your email id in get every New post by mail
ReplyDeletebox and click to submit.Thanks
very nice article.
ReplyDeleteMsg 208, Level 16, State 1, Line 1
ReplyDeleteInvalid object name 'comdetail'.
you need to create comdetail table ,a comdetail is name of table .so use your table name
DeleteWhat are the column that are used in the creation of the company table and commdetail tables
ReplyDeleteyou can use below script for creating company table
Delete-------------------------------------------------------------
create table company(comid int, comname varchar(100))
insert into company (comid,comname) values (1,'asian')
insert into company (comid,comname) values (2,'india paints')
insert into company (comid,comname) values (2,'colursPaints')
-----------------------------------------------------------------------
create comdetail table by using below script
----------------------------------------------------------------------------------
select * into comdetail from ( select comname,Names,Quantity from color a inner join company c on
c.comid=a.comid inner join Quantity b on a.id=b.id )
DataTable PIVOT ( SUM(Quantity) FOR Names IN ( black,red,Grey,wt,re ) ) PivotTable
---------------------------------------------------------------------------------------------------
Good day,
ReplyDeleteI have a question, created a unpivot and i want the result of the unpivot the way i created a select before creating the unpivot, coz my result now is sorter alphabetically
thanks