----First Table:----With Zeroes


can any body help me.........
iss_quantity  received_qty  moved_qty     alloc_qty
------------------------------ --------------------
14            0              0              0
0             0              0              0
0             0              13.7           0
0	      0		     0     	   13
14            0              0              0
14            0              0              0
0	      40.9           0     	    0
14            0              0              0
0	      0              13.8           0
0    	      27.6           0	 	    0
0             0              13.8           0
0             0              13.7           0
0             0              13.8           0
0	      41.4           0     	    0
0	      0              13.9           0
0	      0     	     0     	    13
14            0              0              0



----Resultant Table:----Without Zeroes

iss_quantity  received_qty  moved_qty     alloc_qty
------------------------------ --------------------
14		40.9		13.7		13
14		27.6		13.8		13
14		41.4		13.8		
14				13.7			
14				13.8
				13.9

quote:

---

Originally posted by RickD

So how do you split these into their groups?!?!?!

---

quote:

---

Originally posted by derrickleggett

SELECT
iss_quantity,
CASE WHEN received_qty = 0 THEN '' ELSE received_qty END received_qty,
CASE WHEN moved_qty = 0 THEN '' ELSE moved_qty END moved_qty,
CASE WHEN alloc_qty = 0 THEN '' ELSE alloc_qty END alloc_qty
FROM quantities
WHERE
(received_qty <> 0
OR moved_qty <> 0
OR alloc_qty <> 0)

This should be really efficient also. (cough, cough)


MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

---

DECLARE @quantities TABLE(
	iss_quantity INT,
	received_qty INT,
	moved_qty INT,
	alloc_qty INT)

INSERT @quantities(iss_quantity, received_qty, moved_qty, alloc_qty)
	SELECT 14,0,0,0
	UNION ALL
	SELECT 0,0,0,0
	UNION ALL
	SELECT 0,0,13.7,0
	UNION ALL
	SELECT 14,27.6,0,5

SELECT
	CASE WHEN iss_quantity = 0 THEN '' ELSE CAST(iss_quantity AS VARCHAR(55)) END iss_quantity,
	CASE WHEN received_qty = 0 THEN '' ELSE CAST(received_qty AS VARCHAR(55)) END received_qty,
	CASE WHEN moved_qty = 0 THEN '' ELSE CAST(moved_qty AS VARCHAR(55)) END moved_qty,
	CASE WHEN alloc_qty = 0 THEN '' ELSE CAST(alloc_qty AS VARCHAR(55)) END alloc_qty
FROM @quantities
WHERE
	(received_qty <> 0
		OR moved_qty <> 0
		OR alloc_qty <> 0)