Category    Code
4006        Aug DM 
4006        Aug RM
4006        Sep DM
4006        Sep RM
4006        Oct DM
4007        Oct RM
4007        BT112004
4007        BT122004

Category   TextString
4006       Aug DM,Aug RM,Sep DM,Sep RM,Oct DM
4007       Oct RM,BT112004,BT122004

Declare @myTable table (a int, b varchar(100), list varchar(100))
insert into @myTable (a,b)
Select 4006, 'Aug DM'
Union Select 4006, 'Aug RM'
Union Select 4006, 'Sep DM'
Union Select 4006, 'Sep RM'
Union Select 4006, 'Oct DM'
Union Select 4007, 'Oct RM'
Union Select 4007, 'BT112004'
Union Select 4007, 'BT122004'

Declare @myList varchar(100),
	@lastKey int
Set @lastKey = 0

update @myTable
Set 
	@myList = case when @lastKey<>a then b else @myList + ', ' + b end,
	@lastKey = a,
	list = @myList
From @myTable

Select Z.*
From @myTable Z
Inner Join (Select a, listLen = max(len(list)) From @myTable Group By a) Y
On Z.a = Y.a
and len(Z.list) = Y.listLen

quote:

---

Originally posted by X002548

Just don't blow out on a size limit....



Brett

8-)

---

Declare @myList varchar(100),
	@lastKey int
Set @lastKey = null

update @myTable
Set 
	@myList = case when isnull(@lastKey,a+1)<>a then b else @myList + ', ' + b end,
	@lastKey = a,
	list = @myList
From @myTable

quote:

---

Originally posted by rockmoose

The update technique is truly black magic
Check this alternative: [url]http://sqlteam.com/item.asp?ItemID=2368[/url]
You can wrap that technique in an udf.
Just so You have options...

rockmoose

---

update @myTable --(table variable in corey's example, but could be regular or temporary table)
Set 
	@myList = case when @lastKey<>a then b else @myList + ', ' + b end, --<--here a new value is assigned/appended to @list for every row in the table
	@lastKey = a,
	list = @myList --<--then this column is updated to the value in @list
From @myTable
Is this documented anywhere? dont think so..., part of SQL standard?, don't think so...,
feature?, or just "magic"???

quote:

---

Originally posted by rockmoose

It's an advanced technique that is not taught in class, and afaik it is not known/used by many people.

update @myTable --(table variable in corey's example, but could be regular or temporary table)
Set 
	@myList = case when @lastKey<>a then b else @myList + ', ' + b end, --<--here a new value is assigned/appended to @list for every row in the table
	@lastKey = a,
	list = @myList --<--then this column is updated to the value in @list
From @myTable
Is this documented anywhere? dont think so..., part of SQL standard?, don't think so...,
feature?, or just "magic"???

rockmoose

---

quote:

---

@variable
Is a declared variable that is set to the value returned by expression.

SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.

---

quote:

---

Originally posted by spirit1

acctually if u read carefuly about update in bol you find this:

quote:

---

@variable
Is a declared variable that is set to the value returned by expression.

SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.

---

OK, Mladen, you're sapping my sense of awe and wonder

Hey, just kidding....nice point, though. I think I'll peruse the online tome and go BOL-ing! (Get it?)

Now where did I put my BOL-ing bag?
Go with the flow & have fun! Else fight the flow

---

quote:

---

Originally posted by spirit1

go BOL-ing... hehe. nice one!!
don't let the BOL-ing cd drop on your foot... all that weight you know...you might get hurt

Go with the flow & have fun! Else fight the flow

---