SQL Server 2000 result set grouping

Question

Post reply

SQL Server 2000 result set grouping

cpfaff

Valued Member

Points: 55
More actions
May 2, 2008 at 1:19 pm

#72030

I am trying to display a result set in a grouped format with nulls for redundant data. This has to be much easier than I am making it.
For example, I want to change this result set...
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3
Into…
A 1
2
3
B 1
2
3
C 1
2
3
Anyone have any ideas?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

In theory...

[font="Courier New"]CREATE TABLE #MyTable (GroupCode CHAR(1), Num INT)

INSERT #MyTable

SELECT 'A',1

UNION SELECT 'A',2

UNION SELECT 'A',3

UNION SELECT 'B',1

UNION SELECT 'B',2

UNION SELECT 'B',3

UNION SELECT 'B',4

UNION SELECT 'C',1

UNION SELECT 'C',2

SELECT CASE WHEN Num = (SELECT MIN(X.Num) FROM #MyTable X WHERE X.GroupCode = T.GroupCode) THEN GroupCode ELSE NULL END

, T.Num

FROM #MyTable T

ORDER BY T.GroupCode, T.Num[/font]

In practice I would have to say that this should be done in the client rather than with T-SQL.

cpfaff Valued Member Points: 55 More actions · Answer 2

Thanks for your reply. I agree that it probably should be in done in the client ( in this case a gridview in .NET) but I am stubborn and I want to figure out a way to do it in T-SQL.

I am currently doing something similar to your suggestion except conditionally inserting into a #tmp table using a case statement to see if the groupcode has already been inserted. If it has, then insert a null and use a third column for grouping reference.

I just thought that there had to be a simpler way using ROLLUP or CUBE or some fancy GROUP BY or something. Maybe not.

Thanks very much for the suggestion.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Here's something similar to what I suggested in this thread:

[font="Courier New"]CREATE TABLE #MyTable (GroupCode CHAR(1), Num INT)

GO

INSERT INTO #MyTable

SELECT 'A',1

UNION SELECT 'A',2

UNION SELECT 'A',3

UNION SELECT 'B',1

UNION SELECT 'B',2

UNION SELECT 'B',3

UNION SELECT 'B',4

UNION SELECT 'C',1

UNION SELECT 'C',2

DECLARE @table TABLE (row_id INT IDENTITY, groupcode CHAR(1), Num INT)

INSERT INTO @table

(

groupcode,

num

)

SELECT

groupcode,

num

FROM

#myTable

UPDATE T

SET groupcode = NULL

FROM

@table T JOIN

(SELECT

groupcode,

MIN(row_id) AS keep_row

FROM

@table

GROUP BY

groupcode

) T2 ON

T.groupcode = T2.groupcode AND

T.row_id > keep_row

SELECT * FROM @table

DROP TABLE #mytable[/font]

As a side note you should really put SQL 2000 questions in a SQL 2000 forum. I almost gave you a SQL 2005 answer because you are in a 2005 forum.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

The temp table was so I had some sample data and schema. The select statement against your table should work on it's own.

cpfaff Valued Member Points: 55 More actions · Answer 5

Sorry about the wrong forum post...didnt notice that.

Thanks much for the example.