Multiple rows into a single rows 1

[Danster]

Gday all,

I've just been browsing this forum but cvan't find the appropriate answer.

I need a view to combine multiple rows into a single row.

My table is thus:

Key Field
1 AAA
1 BBB
2 CCC
2 AAA
3 BBB
3 AAA
4 DDD
4 CCC

and need it to display

1 AAA BBB
2 CCC AAA
3 BBB AAA
4 DDD CCC

It needs to be a view.

cheers

Danny

 

[Katy44]

This has often caused me problems, as there is no way to aggregate a string function. The best way I have found is to write a user defined function (hope you're using 2000), which, when passed in a key loops through and returns a string of all the [Field]s concatenated. You then just select distinct Key, newFunction(Key) from your table.
Let me know if you want more details, and if anyone knows of a better way I would love to hear it!

 

[Danster]

Sorry katy44, I can't use a UDF, must be a view.

There is a solution surely using nested queries?

Danster

 

[Danster]

All,

Didn't have time to wait until I had an answer!

Spent the last few hours fiddling & have finally found my solution.

 

[DrAardvark]

I always use cursors to deal with this. Define a select for the cursor and step through turning the rows into columns. The only proviso is that you need to know your maximum number of columns or you need to get into heavy duty stuff where you build the table in code too. The cursors will work in a view I believe ...........

DECLARE Line_Cursor CURSOR
FOR
SELECT Fields
FROM Table
WHERE Conditions
order by Take your pick

OPEN Line_Cursor
FETCH NEXT FROM Line_Cursor
INTO @Import_Text

WHILE @@FETCH_STATUS = 0
BEGIN

Do Stuff Here

END

FETCH NEXT FROM Line_Cursor
INTO @Import_Text

END

 

[KLang]

This isn't pretty, and I would recommend analyzing the execution against using a cursor, but is gives an example of how to concatenate values into a variable given a distinct predicate withoug a cursor. Depeending on the size of the result set, you could consume a siginifncant amount of memory. You can play around the the concatenation to get commas, sizing, etc.


Set NoCount ON
Declare @SQLString Varchar(500)
Declare @Id int
Declare @VTB1 Table (Id int Primary Key, LongName Varchar(500) NULL )
-- Get your unique set of Ids
Insert into @VTB1 (Id) Select Distinct Id From T1 Order By Id

Select @Id = Min(Id) From @VTB1
While @Id is NOT NULL
Begin
Set @SQLString = ''
Select @SQLString = @SQLString + Name From T1 Where Id = @Id
Update @VTB1 Set LongName = @SQLString Where Id = @Id
Set @Id = (Select Top 1 Id From @VTB1 Where Id > @Id)
End
Select * From @VTB1