How to query by grouping one column

yitongzhang · #1 (**permalink**) 01-05-05, 11:55

Hello, everyone:

I have a table like:

Col1 Col2
1 A
2 B
1 D
1 P
2 F
2 W

How to query this table to return by Col1 like

Col1 Col2
1 A,D,P
2 B,F,W

Thanks a lot
ZYT

Brett Kaiser · #2 (**permalink**) 01-05-05, 12:24

Code:

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 char(1))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 1, 'D' UNION ALL
SELECT 1, 'P' UNION ALL
SELECT 2, 'F' UNION ALL
SELECT 2, 'W' UNION ALL
SELECT 3, 'X' UNION ALL
SELECT 3, 'Y' UNION ALL
SELECT 4, 'Z' UNION ALL
SELECT 5, 'O'
GO

DECLARE @MAX_Col1 int, @Col1 int, @strCol2 varchar(8000)
DECLARE @rs table(Col1 int, strCol2 varchar(8000))

SELECT @MAX_Col1 = MAX(Col1), @Col1 = MIN(Col1) FROM myTable99

WHILE @MAX_Col1 > = @Col1
  BEGIN
	SELECT @strCol2 = COALESCE(@strCol2 + ', ','') + Col2 FROM myTable99 WHERE Col1 = @Col1
	INSERT INTO @rs(Col1, strCol2) SELECT @Col1, @strCol2
	SELECT @Col1 = MIN(Col1), @strCol2 = null FROM myTable99 WHERE Col1 > @Col1
  END

SELECT * FROM @rs
GO
	
SET NOCOUNT OFF
DROP TABLE myTable99
GO

blindman · #3 (**permalink**) 01-05-05, 12:55

Your code is always so thorough. I bet your desk is spotless.

Brett Kaiser · #4 (**permalink**) 01-05-05, 13:56

Naw, not spotless, nut definetly not a pig sty

I need to stay organized though...I hate to have to remember things...

Except where the tequila is....

blindman · #5 (**permalink**) 01-05-05, 16:12

When you forget that, you probably shouldn't be having any more anyway.

Brett Kaiser · #6 (**permalink**) 01-05-05, 16:55

See!

No Need to remember

mwolf · #7 (**permalink**) 01-06-05, 05:42

To Brett Kaiser
Why so difficult?
You can create function:
CREATE FUNCTION GetAllOnLine(@id int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000)
SET @Result = ''

SELECT @Result = @Result + col2 + ' ,'
FROM YourTable
WHERE col1=@id

RETURN @Result
END

And use it:

SELECT Col1, GetAllOnLine(Col1)
FROM YourTable

Brett Kaiser · #8 (**permalink**) 01-06-05, 09:07

OK...needs a little cleaning up....

Code:

CREATE FUNCTION GetAllOnLine(@id int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000)

SELECT @Result = COALESCE(@Result + ', ','') + col2
FROM myTable99
WHERE col1=@id

RETURN @Result
END
GO

SELECT Col1, dbo.GetAllOnLine(Col1) FROM MyTable99

But don't you double the access paths to the table? One for the UDF and another for the SELECT? If it's a lot of rows I think mine would be more effecient...(though I still would like to know why anyone would like to do this)

Also, I don't usually like to make my UDF's some very specific, I prefer them to be as generic as possible. You could only use this for one very specific case.

Do you mind if I blog your solution as another example?

mwolf · #9 (**permalink**) 01-06-05, 09:26

This is execution plan of my result query:
|--Compute Scalar(DEFINE

[Expr1002]=[dbo].[MyGetAllOnLine2]([Mail].[CC])))
|--Hash Match(Aggregate, HASH

[Mail].[CC]), RESIDUAL

[Mail].[CC]=[Mail].[CC]))
|--Clustered Index Scan(OBJECT

[MWolf_Mail].[dbo].[Mail].[PK_Mail]))
It is better plan that I saw. Unfortunately, specific UDF is pay for good performance.

mwolf · #10 (**permalink**) 01-06-05, 09:38

Oh. Forum engine replace my plan on smile!!!
:-()

Brett Kaiser · #11 (**permalink**) 01-06-05, 10:02

With code tags perhaps...very nice plan..do you mind if I blog this with the appropriate credit?

Code:

This is execution plan of my result query:
  |--Compute Scalar(DEFINE:([Expr1002]=[dbo].[MyGetAllOnLine2]([Mail].[CC])))
       |--Hash Match(Aggregate, HASH:([Mail].[CC]), RESIDUAL:([Mail].[CC]=[Mail].[CC]))
            |--Clustered Index Scan(OBJECT:([MWolf_Mail].[dbo].[Mail].[PK_Mail]))
It is better plan that I saw. Unfortunately, specific UDF is pay for good performance.

mwolf · #12 (**permalink**) 01-06-05, 10:32

If i understood you correctly, you want to post my example somewhere for others to see it and learn (please apologize my bad English)
If i'm right i have no objections else please correct me