UNPIVOT ing using pure T-SQL - SQL Server Forums

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)

UNPIVOT ing using pure T-SQL

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-07-17 : 00:47:53

Hi Frns,

Need small help.

Using source data i able to do PIVOTing. In the same way i need a query without using PIVOT keywords (i.e i need it using pure T-sql).

Pl help me out!

CREATE TABLE [dbo].[Customer](
[customerName] [varchar](50) NULL,
[product] [varchar](50) NULL,
[qty] [smallint] NULL
)

INSERT INTO [Customer]
SELECT 'Tom','Table',3
UNION ALL
SELECT 'Tom','Chair',4
UNION ALL
SELECT 'Tom','Fan',5
UNION ALL
SELECT 'Ryan','Printer',5
UNION ALL
SELECT 'John','Desktop',9

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
)

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

select * from customer
select * from target

Using target i need to get back the Customer Data?

Thanks in Advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-17 : 00:55:03

have you try using PIVOT & UNPIVOT ?

http://msdn.microsoft.com/en-us/library/ms177410.aspx

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-17 : 01:34:36

after you sum up the qty, i think you not able to retrieve back customer ALL detail...
edit, i mean you only able to get back 6 rows of data per customer...cant have detail of 1 customer purchase how many time an item (if you lost you customer table)

here is a way you can retrieve customer back

select c.* 
from customer c join target t on c.customername =t.customer

Hope can help...but advise to wait pros with confirmation...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-17 : 04:58:55

is this u want


SELECT customer,product,qty
FROM target
UNPIVOT (qty FOR product IN (ChairQty,TableQty,FanQty,DesktopQty,PrinterQty,LaptopQty))p

SELECT * FROM 
(
SELECT customer, 'Chair' AS Product ,ChairQty  AS qty FROM target
UNION ALL
SELECT customer, 'Table'  ,TableQty FROM target
UNION ALL
SELECT customer, 'Fan'  ,FanQty FROM target
UNION ALL
SELECT customer, 'Laptop'  ,LaptopQty FROM target
UNION ALL
SELECT customer, 'Desktop'  ,DesktopQty FROM target
UNION ALL
SELECT customer, 'Printer'  ,PrinterQty FROM target
)s WHERE qty IS NOT NULL

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-19 : 18:33:39

Heh... why on Earth do you need "pure" SQL and what do you mean by "pure" SQL? Do you mean ANSI? And why do you need to use "pure" SQL? Surely you don't believe in the myth of "portable" SQL do you?

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-20 : 05:45:51

quote:
Originally posted by Jeff Moden

Heh... why on Earth do you need "pure" SQL and what do you mean by "pure" SQL? Do you mean ANSI? And why do you need to use "pure" SQL? Surely you don't believe in the myth of "portable" SQL do you?

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

I am also surprised by the term "pure T-SQL". May be OP wants the code that works for all versions

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources