July 28, 2009 at 3:33 am
Hi All,
I posted a similar question yesterday but can't seem to work it out.
I am just trying to write a select statement that would group columns and the separate values by a comma.
I have 3 rows with the following columns (CLASS, DATE AND NAME)
CLASS///// DATE//////// NAME
alpha///// 20-02-09//// Jessi
alpha///// 20-02-09//// Derek
beta///// 22-02-09//// Gerald
I can write a statement to group which would return 3 rows...
select class, date, name
from tbl_subject
group by class, date
However, I only want to retun 2 rows so that the names Jessi and Derek are separated by a comma. In other words, I wish to see the result as
CLASS///// DATE//////// NAME
alpha///// 20-02-09//// Jessi, Derek
beta///// 22-02-09//// Gerald
Can anyone please lend a helping hand
Nneka
July 28, 2009 at 2:27 pm
I couldn't figure it out without using a function but here is what I did.
IF (SELECT OBJECT_ID('tempTable')) IS NOT NULL
DROP TABLE tempTable
CREATE TABLE tempTable (class VARCHAR(10), [date] DATETIME, NAME VARCHAR(10))
INSERT INTO tempTable VALUES ('alpha', '02/20/09', 'Jessi')
INSERT INTO tempTable VALUES ('alpha', '02/20/09', 'Derek')
INSERT INTO tempTable VALUES ('alpha', '02/20/09', 'Derek2')
INSERT INTO tempTable VALUES ('alpha', '02/20/09', 'Derek3')
INSERT INTO tempTable VALUES ('alpha', '02/22/09', 'Gerald')
INSERT INTO tempTable VALUES ('alpha', '02/22/09', 'Gerald2')
INSERT INTO tempTable VALUES ('alpha', '02/22/09', 'Gerald3')
INSERT INTO tempTable VALUES ('alpha', '02/23/09', 'Test');
GO
IF (SELECT OBJECT_ID('testCombiningRows')) IS NOT NULL
DROP FUNCTION testCombiningRows
go
CREATE FUNCTION [dbo].[testCombiningRows]
(
@date DATETIME
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @temp AS VARCHAR(100)
SELECT @temp = COALESCE(@temp+',','')+[NAME] FROM tempTable
WHERE [date] = @date
RETURN @temp
END
GO
SELECT class, [date], dbo.testCombiningRows([date])
FROM tempTable
GROUP BY class, [date]
July 29, 2009 at 8:49 am
I recommend reading this article[/url].
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
July 29, 2009 at 2:16 pm
Thanks for the link Jack, interesting article.
Jay
__
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply