SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Need Help With Pivot Function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Alvin_SQL007
Starting Member

11 Posts

Posted - 03/11/2014 :  07:16:29  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 03/11/2014 :  07:58:38  Show Profile  Reply with Quote

;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



output

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






sabinWeb MCP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000