T-SQL Help please

Is there a column which orders the data, or at least identifies which email address comes second?

If so you could do this:

Code: 
SELECT t1.ID, '"' + t1.EmailAddress + ';' + t2.EmailAddress + '"', t1.ID2 FROM TableName t1 
INNER JOIN (SELECT * FROM TableName t2 WHERE OrderingColumn = 2) t2 ON t1.ID = t2.ID
WHERE t1.OrderingColumn = 1

Note: This will NOT be very fast :)
 
Hi Spunkey - there is an identifier however the number of records can vary - my example shows 2 for each 'contact record' however there can be between 1 and 100 (or more!).

Speed isnt' an issue as it's a one-off + I'm running it againt a data warehouse so won't effect prod.

Cheers.
 
If it's a one off you can do it with a cursor loop and some

Code: 
select @ret = COALESCE(@ret + ';', '') + [youremailcolumn]
from [tablename]
where [column1] = @thisloopvariable
group by [column1]

insert into @temptable(column1, newEmailColumn)
values (@thisloopvariable, @ret)
 
Hi Simon - I've tried your code and get the following:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@ret".
Msg 1087, Level 15, State 2, Line 6
Must declare the table variable "@temptable"
 
At the top of Simon's code, add:

Code: 
DECLARE @ret nvarchar(250)
DECLARE @temptable nvarchar(100)

SET @temptable = 'TempTableName'

Obviously you can change TempTableName to be whatever you want.
 
SimonCHere said:
Sorry I assumed you knew how to write the cursor code.
Bear with me and I'll write the rest.
Click to expand...
I attended an advanced t-sql course a few weeks back which covered cursor and scalar functions (and how not to use then ;-))

Forgotten most of it as I rarely use it! Really appreciate the help though :)
 
Code: 
declare @temptable table([column1] int, newemailcolumn varchar(200),[column2] int)
declare @ret varchar(200)
declare @thisloopvariable int

DECLARE cur CURSOR FOR
  select distinct [YourFirstColumn]
  from [yourtablename]

OPEN cur

FETCH NEXT FROM cur INTO @thisloopvariable

WHILE @@FETCH_STATUS = 0
BEGIN
  set @ret=null
  select @ret = COALESCE(@ret + ';', '') + [youremailcolumn]
  from [yourtablename]
  where [YourFirstColumn] = @thisloopvariable
  group by [YourFirstColumn]

  insert into @temptable([column1], newEmailColumn, [Column2])
  select top 1 [YourFirstcolumn], @ret, [YourLastColumn]
  From [yourtablename]
  Where [yourfirstcolumn] = @thisloopvariable

  FETCH NEXT FROM cur INTO @thisloopvariable
END

CLOSE cur
DEALLOCATE cur

select * from @temptable

You will have to change all the column and table names though!
This is based on the table in your first post.
And you probably want to use nvarchar rather than varchar as well.
 
Last edited:
I have a similar thing in my db's. I use a scaler-function to do it and it runs pretty darn smoothly:

Code: 
CREATE Function [dbo].[fn_getEmailList] (@myID int)
 RETURNS nvarchar(max) AS 
BEGIN
 DECLARE @Result nvarchar(max)
 SET @Result = ''
 SELECT @Result = @Result 
       + CASE WHEN LEN(@Result)>0 THEN ', ' ELSE '' END
       + ltrim(rtrim(myTab.Email))
    FROM myTab
 WHERE myTab.ID = @myID
    ORDER BY myTab.Email;
 RETURN @Result
END

Then, to call it:
Code: 
SELECT myTab.ID, dbo.fn_getEmailList(myTab.ID) as emailList, myTab.etc
FROM myTab
 
You must log in or register to reply here.
Back
Top Bottom