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 (https://blog.sqlauthority.com), SQL SERVER – UNPIVOT Table Example, SQL SERVER – PIVOT Table Example
123 Comments. Leave new
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
This helped me most considerably.
Very much useful this article .
Very much useful article!.
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?
Please post some sample data with expected result
how can I round an aggregated columns value?
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
Nice post!
Can we avoid to sort the data in the pivot as pivot sorts the data autmatically
Nice Article.. for those who have not used pivot before.
Can we avoid to sort the data in the pivot as pivot sorts the data autmatically
help please
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.
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
??
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
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
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;
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)
Could be that you are adding the ‘END’ statement prior to calling the @DateTable after your WHILE loop. Seems like the @DateTable becomes out of scope.
Could be that your @DateTable is falling out of scope due to that ‘END’ statement for your WHILE loop.
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
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
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?
Dear Sir, Please help me out as i have a column name which have space and it is giving error
my query as below:
SELECT *
FROM (
SELECT GrossAmount,ZoneName FROM PROJECTVIEW inner join ZoneMaster on PROJECTVIEW.ZoneID=ZoneMaster.ZoneID
) as s
PIVOT
(
SUM(GrossAmount)
FOR [ZoneName] IN (East,West,North,South,North East)
)AS pvt
—————North East it is giving error because of space , Please help me out