concatenate recordset into one row
-
I am working on an asp script that pulls data from ms sql. In one part, it pulls the days of the week that an event occurs on. If I do something like this: select dayofweek from schedule where id=5 I get: --------- dayofweek --------- M --------- T --------- W --------- What I need is a sql statement that does something like this: --------- dayofweek --------- M,T,W --------- Is there a way to select them into one record like this even though they are stored in multiple rows in the table? Thanks, Jim
-
I am working on an asp script that pulls data from ms sql. In one part, it pulls the days of the week that an event occurs on. If I do something like this: select dayofweek from schedule where id=5 I get: --------- dayofweek --------- M --------- T --------- W --------- What I need is a sql statement that does something like this: --------- dayofweek --------- M,T,W --------- Is there a way to select them into one record like this even though they are stored in multiple rows in the table? Thanks, Jim
Try this T SQL script (you ca make a SQL Server function) --parameter @id DECLARE @id INT SET @id = 5 DECLARE @ssum NVARCHAR(255) DECLARE @dayofweek NVARCHAR(25) SET @ssum = '' DECLARE crs CURSOR FOR SELECT DAYOFWEEK FROM schedule WHERE ID = @id OPEN crs FETCH NEXT FROM crs INTO @dayofweek WHILE( @@FETCH_STATUS = 0 ) BEGIN SET @ssum = @ssum + ',' + @dayofweek FETCH NEXT FROM crs INTO @dayofweek END CLOSE crs DEALLOCATE crs IF( LEN(@ssum) != 0 ) SET @ssum = SUBSTRING(@ssum,2,255) PRINT @ssum
-
Try this T SQL script (you ca make a SQL Server function) --parameter @id DECLARE @id INT SET @id = 5 DECLARE @ssum NVARCHAR(255) DECLARE @dayofweek NVARCHAR(25) SET @ssum = '' DECLARE crs CURSOR FOR SELECT DAYOFWEEK FROM schedule WHERE ID = @id OPEN crs FETCH NEXT FROM crs INTO @dayofweek WHILE( @@FETCH_STATUS = 0 ) BEGIN SET @ssum = @ssum + ',' + @dayofweek FETCH NEXT FROM crs INTO @dayofweek END CLOSE crs DEALLOCATE crs IF( LEN(@ssum) != 0 ) SET @ssum = SUBSTRING(@ssum,2,255) PRINT @ssum
-
Try this T SQL script (you ca make a SQL Server function) --parameter @id DECLARE @id INT SET @id = 5 DECLARE @ssum NVARCHAR(255) DECLARE @dayofweek NVARCHAR(25) SET @ssum = '' DECLARE crs CURSOR FOR SELECT DAYOFWEEK FROM schedule WHERE ID = @id OPEN crs FETCH NEXT FROM crs INTO @dayofweek WHILE( @@FETCH_STATUS = 0 ) BEGIN SET @ssum = @ssum + ',' + @dayofweek FETCH NEXT FROM crs INTO @dayofweek END CLOSE crs DEALLOCATE crs IF( LEN(@ssum) != 0 ) SET @ssum = SUBSTRING(@ssum,2,255) PRINT @ssum
I need to do exactly the same thing, but my result needs to print in a textbox in a SQL Server 2000 Reporting Services Report. I created a function in the Custom Code area for the report, but I get the message: "Statement is not valid inside a method." Can you tell me how to make your code work in Reporting Services?:confused:
-
I need to do exactly the same thing, but my result needs to print in a textbox in a SQL Server 2000 Reporting Services Report. I created a function in the Custom Code area for the report, but I get the message: "Statement is not valid inside a method." Can you tell me how to make your code work in Reporting Services?:confused:
I want to say that you must create a "user defined function" (T SQL function) like this
CREATE FUNCTION SSum (@id INT) //SSum = string sum RETURNS NVARCHAR(255) AS BEGIN DECLARE @ssum NVARCHAR(255) DECLARE @dayofweek NVARCHAR(25) SET @ssum = '' DECLARE crs CURSOR FOR SELECT DAYOFWEEK FROM schedule WHERE ID = @id OPEN crs FETCH NEXT FROM crs INTO @dayofweek WHILE( @@FETCH_STATUS = 0 ) BEGIN SET @ssum = @ssum + ',' + @dayofweek FETCH NEXT FROM crs INTO @dayofweek END CLOSE crs DEALLOCATE crs IF( LEN(@ssum) != 0 ) SET @ssum = SUBSTRING(@ssum,2,255) RETURN @ssum ENDAfter, built your report on a SELECT query using SSum function like this:SELECT DISTINCT ID , dbo.SSum(id) AS Days FROM scheduleVB6,C# -- modified at 17:46 Wednesday 31st August, 2005 -
I want to say that you must create a "user defined function" (T SQL function) like this
CREATE FUNCTION SSum (@id INT) //SSum = string sum RETURNS NVARCHAR(255) AS BEGIN DECLARE @ssum NVARCHAR(255) DECLARE @dayofweek NVARCHAR(25) SET @ssum = '' DECLARE crs CURSOR FOR SELECT DAYOFWEEK FROM schedule WHERE ID = @id OPEN crs FETCH NEXT FROM crs INTO @dayofweek WHILE( @@FETCH_STATUS = 0 ) BEGIN SET @ssum = @ssum + ',' + @dayofweek FETCH NEXT FROM crs INTO @dayofweek END CLOSE crs DEALLOCATE crs IF( LEN(@ssum) != 0 ) SET @ssum = SUBSTRING(@ssum,2,255) RETURN @ssum ENDAfter, built your report on a SELECT query using SSum function like this:SELECT DISTINCT ID , dbo.SSum(id) AS Days FROM scheduleVB6,C# -- modified at 17:46 Wednesday 31st August, 2005