quote:

---

Originally posted by planetoneautomation

If a query were to return the following:


BLUE 15 31
BLUE 22 19
BLUE 43 57
RED 19 25
RED 22 98
RED 34 87

Is it possible to have the query display it like this:

Note: please consider the x's below to be spaces - couldn't get this post to appear correctly using actual spaces.


BLUE 15 31
xxxx 22 19
xxxx 43 57
RED 19 25
xxxx 22 98
xxxx 34 87

That is, to replace the redundant BLUE's and RED's with spaces?

Thanks

---

SELECT
	CASE
		WHEN RowNum = 1
		THEN [Application]
		ELSE 'xxx'
	END as [Application],
	Severity,
	[Defect ID],
	[Detected on Date],
	[Status],
	[Testing Type],
	[Detected By],
	[Assigned To],
	Summary
FROM
(
	SELECT
		BUG.BG_PROJECT as 'Application',
		BUG.BG_SEVERITY as 'Severity',
		BUG.BG_BUG_ID as 'Defect ID',
		BUG.BG_DETECTION_DATE as 'Detected on Date',
		BUG.BG_STATUS as 'Status',
		BUG.BG_USER_03 as 'Testing Type',
		BUG.BG_DETECTED_BY as 'Detected By',
		BUG.BG_RESPONSIBLE as 'Assigned To',
		BUG.BG_SUMMARY as 'Summary',
		ROW_NUMBER() OVER (PARTITION BY BUG.BG_PROJECT ORDER BY BUG.BG_SEVERITY) AS RowNum
	FROM BUG
	WHERE
		BUG.BG_DETECTION_VERSION = '@Environment@' AND
		BUG.BG_DETECTION_DATE BETWEEN '@BeginDate@' AND '@EndDate@' AND
		BUG.BG_STATUS <> 'Closed' AND
		BUG.BG_STATUS <> 'Script Error' AND
		BUG.BG_SEVERITY <> '5-Enhancement'
	ORDER BY 'Application', 'Severity'
) AS T

quote:

---

Originally posted by Lamprey

If yo have SQL 2005 for later you could use the ROW_NUMBER() function. For example (note this is untested):

SELECT
	CASE
		WHEN RowNum = 1
		THEN [Application]
		ELSE 'xxx',
	END as [Application],
	Severity,
	[Defect ID],
	[Detected on Date],
	[Status],
	[Testing Type],
	[Detected By],
	[Assigned To],
	Summary
FROM
(
	SELECT
		BUG.BG_PROJECT as 'Application',
		BUG.BG_SEVERITY as 'Severity',
		BUG.BG_BUG_ID as 'Defect ID',
		BUG.BG_DETECTION_DATE as 'Detected on Date',
		BUG.BG_STATUS as 'Status',
		BUG.BG_USER_03 as 'Testing Type',
		BUG.BG_DETECTED_BY as 'Detected By',
		BUG.BG_RESPONSIBLE as 'Assigned To',
		BUG.BG_SUMMARY as 'Summary',
		ROW_NUMBER() OVER (ORDER BY BUG.BG_PROJECT, BUG.BG_SEVERITY) AS RowNum
	FROM BUG
	WHERE
		BUG.BG_DETECTION_VERSION = '@Environment@' AND
		BUG.BG_DETECTION_DATE BETWEEN '@BeginDate@' AND '@EndDate@' AND
		BUG.BG_STATUS <> 'Closed' AND
		BUG.BG_STATUS <> 'Script Error' AND
		BUG.BG_SEVERITY <> '5-Enhancement'
	ORDER BY 'Application', 'Severity'
) AS T

---

SELECT Col1, Col2, Col3
FROM (
		SELECT Col1, Col2, Col3, Col1 AS Col1Order 
		  FROM myTable99 o 
		 WHERE EXISTS (
				  SELECT Col1, MIN(Col2)
					FROM myTable99 i
				   WHERE o.Col1 = i.Col1
				GROUP BY Col1
				  HAVING o.Col2 = MIN(Col2))

		UNION ALL

		SELECT '' AS Col1, Col2, Col3, Col1 AS Col1Order  
		  FROM myTable99 o 
		 WHERE NOT EXISTS (
				  SELECT Col1, MIN(Col2)
					FROM myTable99 i
				   WHERE o.Col1 = i.Col1
				GROUP BY Col1
				  HAVING o.Col2 = MIN(Col2))
) AS XXX
ORDER BY Col1Order, Col2, Col3

Col1       Col2        Col3
---------- ----------- -----------
BLUE       15          31
           22          19
           43          57
RED        19          25
           22          98
           34          87

(6 row(s) affected)