Need Help With Pivot Function - 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)

Need Help With Pivot Function

Author

Topic

Alvin_SQL007
Starting Member

12 Posts

Posted - 2014-03-11 : 07:16:29

HI ALL,

I have a table as following

CREATE TABLE #CustOrder (CustID INT, ProductType VARCHAR(50),ProductName VARCHAR(50))

INSERT INTO #CustOrder VALUES (1, 'SPORTS','Racket')
INSERT INTO #CustOrder VALUES (1, 'SPORTS','Bat')
INSERT INTO #CustOrder VALUES (1, 'SPORTS','Ball')
INSERT INTO #CustOrder VALUES (1, 'BOOKS','Book55676')
INSERT INTO #CustOrder VALUES (1, 'BOOKS','Book99')
INSERT INTO #CustOrder VALUES (2, 'SPORTS','Ball')
INSERT INTO #CustOrder VALUES (2, 'DECORATIVE','Balloons')

desired T-SQL output is as following

CustID---------SPORTS---------BOOKS-----------DECORATIVE
---1-----------Racket---------Book55676-----------NULL----
---1------------Bat------------Book99--------------NULL----
---1------------Ball-------------NULL--------------NULL----
---2------------Ball-------------NULL-------------Balloons-

How can i achieve this using T-sql. Any help is appreciated.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-11 : 07:58:38

[code]
;with aCTE
AS
( select 1 as CustID , 'SPORTS' as ProductType,'Racket' as ProductName union all
select 1, 'SPORTS','Bat' union all
select 1, 'SPORTS','Ball' union all
select 1, 'BOOKS','Book55676' union all
select 1, 'BOOKS','Book99' union all
select 2, 'SPORTS','Ball' union all
select 2, 'DECORATIVE','Balloons')
,
sportCTE AS(
select CustID, ProductName, Row_Number() OVER(Partition by CustID Order by ProductName) as rn
from aCTE
where ProductType='SPORTS'
GROUP BY CustID, ProductName)
,booksCTE AS (
select CustID, ProductName,Row_Number() OVER(Partition by CustID Order by ProductName) as rn
from aCTE
where ProductType='BOOKS'
GROUP BY CustID, ProductName)

,decorativeCTE AS (
select CustID, ProductName,Row_Number() OVER(Partition by CustID Order by ProductName) as rn
from aCTE
where ProductType='DECORATIVE'
GROUP BY CustID, ProductName)

SELECT C.CustID
,S.ProductName as [SPORTS]
,B.ProductName as [BOOKS]
,D.ProductName as [DECORATIVE]
FROM
(SELECT CustID,Row_Number() OVER(Partition by CustID Order by ProductName) as rn
FROM aCTE) AS C
LEFT JOIN
(SELECT CustID,ProductName,rn
FROM sportCTE) as S ON S.CustID=C.CustID AND S.rn=C.rn
LEFT JOIN
(SELECT CustID,ProductName,rn
FROM booksCTE) as B ON B.CustID=C.CustID AND B.rn=C.rn
LEFT JOIN
(SELECT CustID,ProductName,rn
FROM decorativeCTE) as D ON D.CustID=C.CustID AND D.rn=C.rn
WHERE
COALESCE(S.CustID,B.CustID,D.CustID) Is Not Null

[/code]

output
[code]
CustID SPORTS BOOKS DECORATIVE
1 Ball Book55676 NULL
1 Bat Book99 NULL
1 Racket NULL NULL
2 Ball NULL Balloons
[/code]

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources