October 27, 2005 at 6:06 am
I am trying to create a report where the data looks like the following:
| Mar '02 | 30 | 120 | 25.7 | 134.05 | 11.65 | 145.7 | 3Ps, 4T, 4Vn, | Groundfish, Crab | MCS |
| 4Vs, 4W, 4X, | Scallop, Lobster, | RAD | |||||||
| 5Y, 5Ze | Seal | SOV |
The data in the last three columns comes from multiple rows in the tables, for example, the actual data looks like this: (space between each piece of data represents a new column)
BKSF00-158 1 C 3Ps G 612 SL
BKSF03-158 2 C 4T G 700 NULL
BKSF00-155 3 C 4W G 199 NULL
BKSF00-160 1 O 5Ze P 199 NC
BKSF00-160 2 O 4Vn P 199 NULL
BKSF00-161 1 O 5Y P 199 NULL
How would one return all these values from multiple records in the tables if they are in the same column?
Also, my data contains SQL server DATETIME values and I want to return a sum of the various data for each month. How could I do this on a month by month basis?
Any/all help would be greatly appreciated. Thanks.
October 27, 2005 at 9:48 am
You can create a function that concatenates the values.
--Create Test Table
create table ThatTable (Col1 varchar(10), Col2 char(1))
insert into ThatTable (col1, col2)
select '3Ps','G' union
select '4T','G' union
select '4W','G' union
select '5Ze','P' union
select '4Vn','P' union
select '5Y','P'
-- Create the function, you'll need 1 for each different column you want to do this to
CREATE FUNCTION concatFields
(@id char(1))
RETURNS varchar(1000)
AS
BEGIN
Declare @String varchar(1000)
set @String = ''
-- Concatenation takes place Here
select @String = @String + col1 + ','
from thattable
where col2 = @ID
-- Remove last comma
If @String <> ''
set @String = substring(@String,1,len(@String) -1)
Return @String
END
GO
-- Execute function by itself
select dbo.concatFields ('G')
-- Or you can put it inline with the parent table
select foo, bar, dbo.concatFields (ID) as FooDesc
From Mytable
Where...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply