cancat strings from same columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

cancat strings from same columns

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-06-11 : 15:11:28

Hi, every one,

I have a need to concat strings from a column based on the value from another column.

How my SELECT will look like if I could get away without using cursor?

Here is my sample data:

Declare @t table(intYear int, txtMonth varchar(8), Cell varchar(50))
Insert into @t values(2005, '9', 'str1')
Insert into @t values(2005, '9', 'str2')
Insert into @t values(2005, '10', 'str3')
Insert into @t values(2006, '2', 'str4')
Insert into @t values(2006, '5', 'str5')
Insert into @t values(2005, '5', 'str6')

Here is the Select result I want to see
2005, 9, 'str1 str2'
2005, 10, 'str3'
2006, 2, 'str4'
2006, 5, 'str5 str6'

Or worse case, Update to @t to let it looks like this:
2005, 9, 'str1 str2'
2005, 9, 'str1 str2'
2005, 10, 'str3'
2006, 2, 'str4'
2006, 5, 'str5 str6'
2006, 5, 'str5 str6'

Thanks!
P.S. Preferably, the order in the string is str5 str6, but not str6 str5.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-11 : 16:14:57

Create a function that takes the intYEar and txtMonth values and returns a concatenated list of cell's.


CREATE function dbo.fnGetCellValues (@intYear int, @TxtMonth int)
RETURNS Varchar(100)
AS
BEGIN
	Declare @val varchar(100)
	SET @val = ''
	
	SELECT @val = ISNULL(@val, '') +  ',' + cell
	FROM TestD T 
	Where T.intYear = @intYear AND T.txtMonth = @TxtMonth
	
	RETURN RIGHT(@val, len(@val)-1) 
END

select *, col4 = dbo.fnGetCellValues(intYear, txtMonth) from yourTest

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-06-11 : 17:00:15

Dinakar,

Thank you!

One thing I want to make sure.

Your TestD in the function seems is the underlying table. However, I am using #table in memory.

Your solution seems require either I pass in the #table to the function (is it possible?), or promote my #table into ##table or something permenant outside of my code session.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-11 : 17:49:19

Hmm.. if the table is dynamic perhaps we cannot use the function. You might have to do it on a row-by-row basis..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-06-11 : 18:04:24

You meant a cursor?

phdiwakar
Starting Member

15 Posts

Posted - 2007-06-11 : 18:10:57

This is not the best solution performance wise, but you can do a self join IF you can have a max of two rows for each year and month combination

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-11 : 18:14:49

Yes it may be a little slow, depending on how many records can bein the table variable. No need to use a cursor. You can use a WHILE loop.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 09:22:07

Where do you want to show data?
If you use front end application, you can easily do this concatenation

Madhivanan

Failing to plan is Planning to fail

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-06-12 : 10:28:56

Ok, SELF JOIN or WHILE LOOP, I will be grateful if can you post a sample.

And, yes, I am pretty sure that any year/month combination will have max of two strings.

The size of the #table is normally 5 to 6 columns (months) by 2 to 3 rows (years), so performance is a less concern.

As long as the idea of letting app deal with this, oddly enough, that is the thing they tried to avoid at this particular case. Adding this element made the front end exponentially complicated to maintain and duplicate.

phdiwakar
Starting Member

15 Posts

Posted - 2007-06-12 : 11:19:19

Hommer,
here you go... I added identity field to the table to distinguish between different rows.


Declare @t table( id int IDENTITY(1,1), intYear int, txtMonth varchar(8), Cell varchar(50))
Insert into @t values(2005, '9', 'str1')
Insert into @t values(2005, '9', 'str2')
Insert into @t values(2005, '10', 'str3')
Insert into @t values(2006, '2', 'str4')
Insert into @t values(2006, '5', 'str5')
Insert into @t values(2005, '5', 'str6')

select a.intYear, a.txtMonth, min(a.Cell+ 
case when b.cell is null then ' ' else ','+ b.Cell end)
from @t a left join @t b on a.intYear=b.intYear
and a.txtMonth=b.txtMonth and a.id <>b.id
group by a.intYear, a.txtMonth

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-12 : 11:23:43

If its only 2 columns max, you would at least need to add an identity column to make a row unique from another for a given combination of year and month. The easiest way is to add an IDENTITY column.


Declare @t table(rowid int identity, intYear int, txtMonth varchar(8), Cell varchar(50))
Insert into @t values(2005, '9', 'str1')
Insert into @t values(2005, '9', 'str2')
Insert into @t values(2005, '10', 'str3')
Insert into @t values(2006, '2', 'str4')
Insert into @t values(2006, '5', 'str5')
Insert into @t values(2006, '5', 'str6')

select * , col4 = Cell + ',' +  (select Top 1 Cell from @t T2 Where T2.intYear = T1.intYear AND T2.txtMonth = T1.txtMonth AND T2.rowid > T1.rowid )
from @t T1

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-06-12 : 12:05:37

Thank you both!

I forgot to add an id field!

Case closed. Hallelujah!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources