PIVOT and UNPIVOT data using sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

PIVOT and UNPIVOT data using sql

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-11-12 : 01:11:37

Hi Pals,

Small help required.

I have sample table called Customer along with some sample data.
I have one target table into which i want to PIVOT the data and insert it.
I am successful in doing that but my question is, using the "Target" table data
i need to get back the original data i.e i need to UNPIVOT the data in the target table.

Can anyone help me out like how does the query look like?

CREATE TABLE [dbo].[Customer](
[customerName] [varchar](50) NULL,
[product] [varchar](50) NULL,
[qty] [smallint] NULL
)
go
insert Customer(customerName,product,qty) values('Tom','Chair',4)
insert Customer(customerName,product,qty) values('Tom','Table',3)
insert Customer(customerName,product,qty) values('Tom','Fan',5)
insert Customer(customerName,product,qty) values('John','Laptop',2)
insert Customer(customerName,product,qty) values('John','Desktop',9)
insert Customer(customerName,product,qty) values('Ryan','Printer',7)
GO
update statistics Customer
GO

CREATE TABLE [dbo].[Target](
[customer] [varchar](50) NULL,
[ChairQty] [smallint] NULL,
[TableQty] [smallint] NULL,
[FanQty] [smallint] NULL,
[LaptopQty] [smallint] NULL,
[DesktopQty] [smallint] NULL,
[PrinterQty] [smallint] NULL
) ON [PRIMARY]

INSERT INTO Target
(Customer,ChairQty,TableQty,FanQty,LaptopQty,DesktopQty,PrinterQty)
SELECT
customerName as customer,
SUM(CASE when product = 'Chair' then qty else 0 end) as ChairQty,
SUM(CASE when product = 'Table' then qty else 0 end) as TableQty,
SUM(CASE when product = 'Fan' then qty else 0 end) as FanQty,
SUM(CASE when product = 'Laptop' then qty else 0 end) as LaptopQty,
SUM(CASE when product = 'Desktop' then qty else 0 end) as DesktopQty,
SUM(CASE when product = 'Printer' then qty else 0 end) as PrinterQty
FROM Customer
GROUP BY customerName

Thank You!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 01:36:09

[code]CREATE TABLE #Customer(
[customerName] [varchar](50) NULL,
[product] [varchar](50) NULL,
[qty] [smallint] NULL
)
go
insert #Customer(customerName,product,qty) values('Tom','Chair',4)
insert #Customer(customerName,product,qty) values('Tom','Table',3)
insert #Customer(customerName,product,qty) values('Tom','Fan',5)
insert #Customer(customerName,product,qty) values('John','Laptop',2)
insert #Customer(customerName,product,qty) values('John','Desktop',9)
insert #Customer(customerName,product,qty) values('Ryan','Printer',7)
GO
--update statistics Customer
GO

CREATE TABLE #Target(
[customer] [varchar](50) NULL,
[ChairQty] [smallint] NULL,
[TableQty] [smallint] NULL,
[FanQty] [smallint] NULL,
[LaptopQty] [smallint] NULL,
[DesktopQty] [smallint] NULL,
[PrinterQty] [smallint] NULL
) ON [PRIMARY]

select * from #Customer
INSERT INTO #Target
(Customer,ChairQty,TableQty,FanQty,LaptopQty,DesktopQty,PrinterQty)
SELECT
customerName as customer,
SUM(CASE when product = 'Chair' then qty else 0 end) as ChairQty,
SUM(CASE when product = 'Table' then qty else 0 end) as TableQty,
SUM(CASE when product = 'Fan' then qty else 0 end) as FanQty,
SUM(CASE when product = 'Laptop' then qty else 0 end) as LaptopQty,
SUM(CASE when product = 'Desktop' then qty else 0 end) as DesktopQty,
SUM(CASE when product = 'Printer' then qty else 0 end) as PrinterQty
FROM #Customer
GROUP BY customerName

select * from #Target

select customer,
REPLACE(Val,'Qty','') AS Val,
Quantity
from (select customer,
NULLIF(ChairQty,0) AS ChairQty,
NULLIF(TableQty,0) AS TableQty,
NULLIF(FanQty,0) AS FanQty,
NULLIF(LaptopQty,0) AS LaptopQty,
NULLIF(DesktopQty,0) AS DesktopQty,
NULLIF(PrinterQty,0) AS PrinterQty from #Target)m
unpivot(Quantity FOR Val IN ([ChairQty],[TableQty],[FanQty],[LaptopQty],[DesktopQty],[PrinterQty]))p

drop table #Target
drop table #Customer

output
------------------------------------------
customer
----------------------------
customerName product qty
Tom Chair 4
Tom Table 3
Tom Fan 5
John Laptop 2
John Desktop 9
Ryan Printer 7

target
------------------------------------
customer ChairQty TableQty FanQty LaptopQty DesktopQty PrinterQty
John 0 0 0 2 9 0
Ryan 0 0 0 0 0 7
Tom 4 3 5 0 0 0

after unpivot
-----------------------------------------------
customer Val Quantity
John Laptop 2
John Desktop 9
Ryan Printer 7
Tom Chair 4
Tom Table 3
Tom Fan 5
[/code]

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-12 : 01:39:26

it is not possible track in reverse way Because u are taking the sum of the items in the target table.

malay

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-12 : 01:40:29

SELECT customer, PRODUCT, qty
FROM TARGET
UNPIVOT(qty FOR PRODUCT IN([ChairQty],[TableQty],[FanQty],[LaptopQty],[DesktopQty],[PrinterQty])) AS u

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 01:43:41

quote:
Originally posted by malaytech2008

it is not possible track in reverse way Because u are taking the sum of the items in the target table.

malay

see above suggestion. its possible

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-12 : 01:44:28

Above query will work only if one entry for each customer for one item.

Visakh , above query will work if same customer has two entry for same items
e.g.
customerName product qty
Tom Chair 4
Tom Table 3
Tom Fan 2
Tom Table 1
Tom Fan 9

malay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 01:44:34

quote:
Originally posted by lionofdezert

SELECT customer, PRODUCT, qty
FROM TARGET
UNPIVOT(qty FOR PRODUCT IN([ChairQty],[TableQty],[FanQty],[LaptopQty],[DesktopQty],[PrinterQty])) AS u

will get some additional rows because of 0 values so need to eliminate them before UNPIVOTing

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 01:48:40

quote:
Originally posted by malaytech2008

Above query will work only if one entry for each customer for one item.

Visakh , above query will work if same customer has two entry for same items
e.g.
customerName product qty
Tom Chair 4
Tom Table 3
Tom Fan 2
Tom Table 1
Tom Fan 9

malay

Nope. you wont get them back as individual items. since you're aggregating on product you will be able to retrieve values as only one record per product.

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-11-12 : 05:12:22

Thank You All!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 05:33:34

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources