[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

MS SQL 2000 cross-tab(pivot) query

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

Open in new window

0
sailing_12
Asked:
sailing_12
  • 5
  • 4
1 Solution
 
pcelbaCommented:
Try this:
SELECT stock_number,
       p.itemid, 
       pricegroup, 
       MAX(CASE WHEN p.pricegroup = 6 THEN price ELSE null END) price6, 
       description,
       MAX(CASE WHEN p.pricegroup = 10 THEN price ELSE null END) price10 
  FROM iod_pricing p
 INNER JOIN IOD_Items i ON p.itemid = i.itemid 
 WHERE p.pricegroup IN (6,10)
   AND usergroupid = 0
 GROUP BY stock_number, p.itemid, pricegroup, description
 ORDER BY i.stock_number

Open in new window

0
 
sailing_12Author Commented:
Not quite - see below. There should be one row for each stock number with the prices shown in the two dedicated columns. We actually don't need to return the pricegroup column or the description for that matter.
stock_number	itemid	pricegroup	Price6	Description	Price10
47-077	17684	6	68	Product 1	NULL
47-077	17684	10	NULL	Product 1	100
47-078	17683	10	NULL	Product 2	118.75
47-078	17683	6	80.75	Product 2	NULL
53-047	6401	10	NULL	Product 3	0
53-123	17430	6	1.53	Product 4	NULL
53-352	17593	6	12.63	Product 5	NULL
53-740	6581	6	150	Product 6	NULL
53-740	6581	10	NULL	Product 6	273.14

Open in new window

0
 
pcelbaCommented:
Of course and sorry.

pricegroup must not be in GROUP BY:
SELECT stock_number, 
       p.itemid,  
       pricegroup,  
       MAX(CASE WHEN p.pricegroup = 6 THEN price ELSE null END) price6,  
       description, 
       MAX(CASE WHEN p.pricegroup = 10 THEN price ELSE null END) price10  
  FROM iod_pricing p 
 INNER JOIN IOD_Items i ON p.itemid = i.itemid  
 WHERE p.pricegroup IN (6,10) 
   AND usergroupid = 0 
 GROUP BY stock_number, p.itemid, description 
 ORDER BY i.stock_number

Open in new window

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
pcelbaCommented:
And pricegroup cannot be in SELECT list....
SELECT stock_number,  
       p.itemid,   
       MAX(CASE WHEN p.pricegroup = 6 THEN price ELSE null END) price6,   
       description,  
       MAX(CASE WHEN p.pricegroup = 10 THEN price ELSE null END) price10   
  FROM iod_pricing p  
 INNER JOIN IOD_Items i ON p.itemid = i.itemid   
 WHERE p.pricegroup IN (6,10)  
   AND usergroupid = 0  
 GROUP BY stock_number, p.itemid, description  
 ORDER BY i.stock_number

Open in new window

0
 
sailing_12Author Commented:
This question is not abandoned.
0
 
pcelbaCommented:
I would say it is answered already.
0
 
sailing_12Author Commented:
Could be. I haven't had a chance to try your latest. We had a record snowstorm here over the weekend and I was out of the office yesterday. EE sent me a notice saying the question appeared abandoned after no activity for three days - A bit hyper-sensitive I'd say.

0
 
pcelbaCommented:
Interesting. I am receiving such notice after 21 days.

I think it is OK, the question can wait :-)
0
 
sailing_12Author Commented:
Looks good. Thanks.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now