formatting variable output
-
I am trying to concatenate variable row values data and put into a semicolon seperated column to create one row for each instance.
output would look like
ID item count items
----------------------
2 4 ba;cr;de;gh
thanks for any suggestions.
do i have to use a cursor?
-
What does the source data look like?
-
table a
ID column (pk)
2
table b
ID(pk) AID(fk) name
1 2 ba
2 2 cr
3 2 de
-
Bit rought around the edges!
Create Table TestII(IDi int,AID INT, [Name] Char(2))
GO
Insert TestII values(1,2,'ba')
Insert TestII values(2,2,'cr')
Insert TestII values(3,2,'de')
Insert TestII values(4,1,'ba')
Insert TestII values(5,3,'ba')
GO
Create Function TestIII(@intAID Int) returns char(100) as
Begin
Declare @v-2 Varchar(100)
Set @v-2=''
Select @v-2=@v+[name]+','
From TestII
Where Aid=@intAid
If DataLength(@v)>0
Set @v-2=Left(@v,DataLength(@v)-1)
Return @v-2
End
GO
Select Distinct AID,dbo.TestIII(AID) From TestII
GO
Drop function testIII
Drop table testII
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply