Group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by

Author

Topic

vinothkumar.kk
Starting Member

5 Posts

Posted - 2012-12-06 : 05:09:56

Hi all,

I have to get column which is not in group column and join together in single column.How to achieve it in sql server 2000.
ex:
tablename
sec student marks
A ram 20
A raj 50
B ramu 80
B dines 99

Output:
sec student max(marks)
A ram,raj 50
B ramu,dines 99

Please how to do it in sql server 2000 I need query ..It's sample data ..my wrk pjt need such like of query

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-06 : 05:20:47

select sec, min(student) + ',' + max(student), max(marks)
from tbl
group by sec

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vinothkumar.kk
Starting Member

5 Posts

Posted - 2012-12-06 : 05:30:58

No of student of each sec is more than 2 i just gave sample data with 2 data for each sec.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-06 : 05:40:45

[code]
GO
CREATE FUNCTION CsvForm ( @p_sec varchar(5))
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @a varchar(4000) = ''
SELECT @a = @a + student + ',' FROM tablename WHERE sec = @p_sec
RETURN LEFT(@a, LEN(@a)-1)
END
GO
SELECT sec, dbo.CsvForm(sec) names, MAX(marks) maxMark
FROM tablename
GROUP BY sec
[/code]

--
Chandu

vinothkumar.kk
Starting Member

5 Posts

Posted - 2012-12-06 : 05:56:15

Hi,

My table is temporary table and hence it cant be used in procedure,functions..how to achieve it in query

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-06 : 06:33:53

Is there possibility to change that temporary table to normal table.. ?

--
Chandu

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-06 : 06:55:56

Does sql server 2000 include xml statements.
Try the example at the top here - think you will need to change the table variable to a temp table

http://www.nigelrivett.net/SQLTsql/CSVStringSQL.html

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vinothkumar.kk
Starting Member

5 Posts

Posted - 2012-12-06 : 07:04:10

Actually am in oracle now am learning sql server ..There are lot of difference in coding how to learn sql server ..It some what clumpsy

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-06 : 07:06:48

quote:
Originally posted by nigelrivett

Does sql server 2000 include xml statements.
Try the example at the top here - think you will need to change the table variable to a temp table

http://www.nigelrivett.net/SQLTsql/CSVStringSQL.html

Hi,
MSSQL 2000 supports only FOR XML RAW, EXPLICIT, AUTO but not PATH

--
Chandu

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-06 : 07:15:33

How about this

create table #a (sec varchar(10), student varchar(10), marks int)
insert #a select 'A', 'ram', 20
insert #a select 'A', 'raj', 50
insert #a select 'B', 'ramu', 80
insert #a select 'B', 'dines', 99
insert #a select 'B', 'xxxx', 10

create table #b (sec varchar(10), student varchar(1000), marks int, laststudent varchar(10))
insert #b select sec, min(student), max(marks), min(student) from #a group by sec
while exists (select * from #a a join #b b on a.sec = b.sec where a.student > b.laststudent)
begin
update b
set student = b.student + ',' + a.student ,
laststudent = a.student
from #b b
join #a a
on a.sec = b.sec
and a.student = (select min(a2.student) from #a a2 where a2.sec = b.sec and a2.student > b.laststudent)
end

select * from #b

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vinothkumar.kk
Starting Member

5 Posts

Posted - 2012-12-07 : 00:51:54

Thanks for your help..Can you explain this query

quote:
Originally posted by nigelrivett

How about this

create table #a (sec varchar(10), student varchar(10), marks int)
insert #a select 'A', 'ram', 20
insert #a select 'A', 'raj', 50
insert #a select 'B', 'ramu', 80
insert #a select 'B', 'dines', 99
insert #a select 'B', 'xxxx', 10

create table #b (sec varchar(10), student varchar(1000), marks int, laststudent varchar(10))
insert #b select sec, min(student), max(marks), min(student) from #a group by sec
while exists (select * from #a a join #b b on a.sec = b.sec where a.student > b.laststudent)
begin
update b
set student = b.student + ',' + a.student ,
laststudent = a.student
from #b b
join #a a
on a.sec = b.sec
and a.student = (select min(a2.student) from #a a2 where a2.sec = b.sec and a2.student > b.laststudent)
end

select * from #b

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-07 : 03:20:18

First insert gets the distinct sec with the max mark and min student.
The loop concatenates the other students associated with the sec.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources