troubleshooting Question

MS SQL 2000 cross-tab(pivot) query

Avatar of Sailing_12
Sailing_12 asked on
DatabasesMicrosoft SQL Server 2005SQL
9 Comments1 Solution285 ViewsLast Modified:
I have the following query which returns the results below. I would like to combine the duplicated stock number rows, such that when the pricegroup = 10, the price column value is appended to the end of the matching pricegroup 6 row instead.

This is a compatibility = 80 DB on MS SQL 2005 so PIVOT will not work, has to be done with CASE.


select stock_number,
         p.itemid,
       pricegroup,
       price,
         description
from iod_pricing p
INNER JOIN IOD_Items i
ON p.itemid = i.itemid
where p.pricegroup IN (6,10)
and usergroupid = 0
order by i.stock_number
stock_number	itemid	pricegroup	price	description
58-018	16699	6	150	Item 1
58-018	16699	10	220.5	Item 1
58-019	16700	6	200	Item 2
58-019	16700	10	294	Item 2
58-020	16701	10	183.75	Item 3
58-020	16701	6	125	Item 3
58-021	16702	6	45	Item 4
58-021	16702	10	66.15	Item 4
58-022	16703	6	100	Item 5
58-022	16703	10	147	Item 5
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros