SQL query
-
I have a table which has Class Name 1A Albert 1A Bill 1A Paul 1B Ricky 1B Kelvin 1B Walala I want to convert it to a table which is Class FULL LIST 1A Albert/Bill/Paul 1B Ricky/Kelvin/Walala I dont know how to write the query. Pls kindly help
Which database are you trying to run this in. If it's SQL Server 2005, you can use a thing called a Common Table Expression to help you out with this. If it's not, (and I apologise to the Oracle guys if they have something whizzbang like CTE's), you will need to write a recursive query. These aren't fun, but examples can be found on Google.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world." Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that." Deja View - the feeling that you've seen this post before.
-
Which database are you trying to run this in. If it's SQL Server 2005, you can use a thing called a Common Table Expression to help you out with this. If it's not, (and I apologise to the Oracle guys if they have something whizzbang like CTE's), you will need to write a recursive query. These aren't fun, but examples can be found on Google.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world." Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that." Deja View - the feeling that you've seen this post before.
-
I have a table which has Class Name 1A Albert 1A Bill 1A Paul 1B Ricky 1B Kelvin 1B Walala I want to convert it to a table which is Class FULL LIST 1A Albert/Bill/Paul 1B Ricky/Kelvin/Walala I dont know how to write the query. Pls kindly help
You could do something like this:
CREATE FUNCTION GetNames (@cClass varchar(2))
RETURNS varchar(1000) AS
BEGINDECLARE @cClassNames varchar(1000)
DECLARE @cClassName varchar(50)SELECT @cClassNames = ''
DECLARE namecursor CURSOR FOR
SELECT DISTINCT Name
FROM classtable
WHERE Class = @cClassOPEN namecursor
FETCH NEXT FROM namecursor INTO @cClassName
WHILE @@FETCH_STATUS = 0
BEGIN
select @cClassNames = @cClassNames + @cClassName +'/'
FETCH NEXT FROM namecursor INTO @cClassName
ENDCLOSE namecursor
DEALLOCATE namecursor
RETURN(@cClassNames)
ENDCall it like this:
select class, dbo.GetNames(class) as [Full List] from (select distinct class from classtable) as tbl
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
You could do something like this:
CREATE FUNCTION GetNames (@cClass varchar(2))
RETURNS varchar(1000) AS
BEGINDECLARE @cClassNames varchar(1000)
DECLARE @cClassName varchar(50)SELECT @cClassNames = ''
DECLARE namecursor CURSOR FOR
SELECT DISTINCT Name
FROM classtable
WHERE Class = @cClassOPEN namecursor
FETCH NEXT FROM namecursor INTO @cClassName
WHILE @@FETCH_STATUS = 0
BEGIN
select @cClassNames = @cClassNames + @cClassName +'/'
FETCH NEXT FROM namecursor INTO @cClassName
ENDCLOSE namecursor
DEALLOCATE namecursor
RETURN(@cClassNames)
ENDCall it like this:
select class, dbo.GetNames(class) as [Full List] from (select distinct class from classtable) as tbl
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
Nice - I was working on one which didn't use cursors (it used stored functions), but there's no need now as yours does the job perfectly.:-D
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world." Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that." Deja View - the feeling that you've seen this post before.
-
You could do something like this:
CREATE FUNCTION GetNames (@cClass varchar(2))
RETURNS varchar(1000) AS
BEGINDECLARE @cClassNames varchar(1000)
DECLARE @cClassName varchar(50)SELECT @cClassNames = ''
DECLARE namecursor CURSOR FOR
SELECT DISTINCT Name
FROM classtable
WHERE Class = @cClassOPEN namecursor
FETCH NEXT FROM namecursor INTO @cClassName
WHILE @@FETCH_STATUS = 0
BEGIN
select @cClassNames = @cClassNames + @cClassName +'/'
FETCH NEXT FROM namecursor INTO @cClassName
ENDCLOSE namecursor
DEALLOCATE namecursor
RETURN(@cClassNames)
ENDCall it like this:
select class, dbo.GetNames(class) as [Full List] from (select distinct class from classtable) as tbl
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters