T-SQL PIVOT Table Assistance Request

Given the following temp table which represents my real data issue, I'm hoping I can create a pivot as follows:

IF OBJECT_ID('tempdb.dbo.#TempTableToPivot') IS NOT NULL
	DROP TABLE #TempTableToPivot
CREATE TABLE #TempTableToPivot
(
	Code VARCHAR(10),
	CodeDescription VARCHAR(100),
	CodePrice DECIMAL(7,2),
	PricingType VARCHAR(10),
	PricingRegion VARCHAR(10)
)
INSERT INTO #TempTableToPivot (Code, CodeDescription, CodePrice, PricingType, PricingRegion)
VALUES ('J1720-100', 'Solu-Cortef (hydrocortisone) 100 mg', 5.10, 'OWCP', 'AL02')
INSERT INTO #TempTableToPivot (Code, CodeDescription, CodePrice, PricingType, PricingRegion)
VALUES ('J1720-100', 'Solu-Cortef (hydrocortisone) 100 mg', 5.10, 'OWCP', 'AR02')
INSERT INTO #TempTableToPivot (Code, CodeDescription, CodePrice, PricingType, PricingRegion)
VALUES ('J1720-100', 'Solu-Cortef (hydrocortisone) 100 mg', 5.10, 'OWCP', 'AZ00')
INSERT INTO #TempTableToPivot (Code, CodeDescription, CodePrice, PricingType, PricingRegion)
VALUES ('J1720-100', 'Solu-Cortef (hydrocortisone) 100 mg', 5.10, 'OWCP', 'GA01')
INSERT INTO #TempTableToPivot (Code, CodeDescription, CodePrice, PricingType, PricingRegion)
VALUES ('J1720-100', 'Solu-Cortef (hydrocortisone) 100 mg', 5.10, 'OWCP', 'IA02')
INSERT INTO #TempTableToPivot (Code, CodeDescription, CodePrice, PricingType, PricingRegion)
VALUES ('J1720-100', 'Solu-Cortef (hydrocortisone) 100 mg', 5.10, 'OWCP', 'IA03')
INSERT INTO #TempTableToPivot (Code, CodeDescription, CodePrice, PricingType, PricingRegion)
VALUES ('J1720-100', 'Solu-Cortef (hydrocortisone) 100 mg', 5.35, 'OWCP', 'DC00')
INSERT INTO #TempTableToPivot (Code, CodeDescription, CodePrice, PricingType, PricingRegion)
VALUES ('J1720-100', 'Solu-Cortef (hydrocortisone) 100 mg', 5.35, 'OWCP', 'PA04')
INSERT INTO #TempTableToPivot (Code, CodeDescription, CodePrice, PricingType, PricingRegion)
VALUES ('J1720-100', 'Solu-Cortef (hydrocortisone) 100 mg', 5.35, 'OWCP', 'WV03')
INSERT INTO #TempTableToPivot (Code, CodeDescription, CodePrice, PricingType, PricingRegion)
VALUES ('J1720-100', 'Solu-Cortef (hydrocortisone) 100 mg', 5.21, 'OWCP', 'PA05')
INSERT INTO #TempTableToPivot (Code, CodeDescription, CodePrice, PricingType, PricingRegion)
VALUES ('J1720-100', 'Solu-Cortef (hydrocortisone) 100 mg', 5.21, 'OWCP', 'NJ01')
INSERT INTO #TempTableToPivot (Code, CodeDescription, CodePrice, PricingType, PricingRegion)
VALUES ('J1720-100', 'Solu-Cortef (hydrocortisone) 100 mg', 5.21, 'OWCP', 'MI03')
INSERT INTO #TempTableToPivot (Code, CodeDescription, CodePrice, PricingType, PricingRegion)
VALUES ('J1720-100', 'Solu-Cortef (hydrocortisone) 100 mg', 5.21, 'OWCP', 'MI01')

SELECT * FROM #TempTableToPivot

Open in new window


Here is the represented table:
Code	CodeDescription	CodePrice	PricingType	PricingRegion
J1720-100	Solu-Cortef (hydrocortisone) 100 mg	5.10	OWCP	AL02
J1720-100	Solu-Cortef (hydrocortisone) 100 mg	5.10	OWCP	AR02
J1720-100	Solu-Cortef (hydrocortisone) 100 mg	5.10	OWCP	AZ00
J1720-100	Solu-Cortef (hydrocortisone) 100 mg	5.10	OWCP	GA01
J1720-100	Solu-Cortef (hydrocortisone) 100 mg	5.10	OWCP	IA02
J1720-100	Solu-Cortef (hydrocortisone) 100 mg	5.10	OWCP	IA03
J1720-100	Solu-Cortef (hydrocortisone) 100 mg	5.35	OWCP	DC00
J1720-100	Solu-Cortef (hydrocortisone) 100 mg	5.35	OWCP	PA04
J1720-100	Solu-Cortef (hydrocortisone) 100 mg	5.35	OWCP	WV03
J1720-100	Solu-Cortef (hydrocortisone) 100 mg	5.21	OWCP	PA05
J1720-100	Solu-Cortef (hydrocortisone) 100 mg	5.21	OWCP	NJ01
J1720-100	Solu-Cortef (hydrocortisone) 100 mg	5.21	OWCP	MI03
J1720-100	Solu-Cortef (hydrocortisone) 100 mg	5.21	OWCP	MI01

Open in new window


Here is what I would like to see from the "pivot", if such a thing is even possible:

Code		CodeDescription							CodePrice	PricingType		PricingRegion
J1720-100	Solu-Cortef (hydrocortisone) 100 mg		5.10		OWCP			AL02, AR02, AZ00, GA01, IA02, IA03
J1720-100	Solu-Cortef (hydrocortisone) 100 mg		5.35		OWCP			DC00, PA04, WV03
J1720-100	Solu-Cortef (hydrocortisone) 100 mg		5.21		OWCP			PA05, NJ01, MI03, MI01

Open in new window


So, I just don't grok PIVOT tables, and looking at online examples, I'm not seeing a clear way to do this. Would someone be able to provide me with the T-SQL for this task, if it's possible?

Thanks!
LVL 20
Daniel Van Der WerkenIndependent ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
There is an easier way to do this without a PIVOT:

SELECT DISTINCT T1.Code, T1.CodeDescription, T1.CodePrice, T1.PricingType,
	STUFF(
	(
		SELECT ', ' + PricingRegion
		FROM #TempTableToPivot AS T2
		WHERE T1.Code = T2.Code
			AND T1.CodePrice = T2.CodePrice
			AND T1.PricingType = T2.PricingType
		ORDER BY PricingRegion
		FOR XML PATH('')
	), 1, 2, '') AS PricingRegion
FROM #TempTableToPivot AS T1

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Daniel Van Der WerkenIndependent ConsultantAuthor Commented:
Perfect. Thanks for the great solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.