Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select All Records from Temporary Table and Assign to VARCHAR? Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 5:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 12, 2013 6:35 AM
Points: 4, Visits: 11
Hello!
I am new to TSQL and require some help with a problem I'm having.

I have a stored procedure where I create a temporary table:

Create Table #TempSourceOutput(
SourceKey varchar(50),
SourceID varchar(50),
SourceComment varchar(max) )

and I can successfully populate it using:
Insert Into #TempSourceOutput (SourceKey, SourceID, SourceComment)
Select @SourceKey, Cast(@SourceID as varchar), @SourceComment

however, I would like to get all the values from this table and list them (along with other variables) in a varchar, resulting in something like this:
Declare @TargetData varchar(max)
Set @TargetData = "Target Project Name"
Set @msg= 'Are you sure you wish to copy the following records to: ' + @TargetData + '?' +
'AA 12 Test Comment'
'AB 13 Another Test Comment'
'AC 14 And another one...'

(where lines above AA, AB, AC are the result set of #TempSourceOutput - ie. columns SourceKey + SourceID + SourceComment)

I would like to return it all as one complete varchar.
Is it at all possible, and if so, how.
Many thanks for your help in advance.

Best wishes
Winnie
Post #1474494
Posted Wednesday, July 17, 2013 5:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
Here's a fairly complete article on how to concatenate row values using different solutions:

Concatenating Row Values in Transact-SQL




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1474499
Posted Wednesday, July 17, 2013 5:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 12, 2013 6:35 AM
Points: 4, Visits: 11
Thank you!
I will read it straight away.
Post #1474502
Posted Wednesday, July 17, 2013 6:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 12, 2013 6:35 AM
Points: 4, Visits: 11
Thank you (again) for your speedy reply.

Using this bit of code:
SELECT DISTINCT [Source Data]
FROM #TempSourceOutput t1
CROSS APPLY ( SELECT SourceKey + ' [' + SourceID +'] '+ SourceComment
FROM #TempSourceOutput t2
WHERE t2.SourceID = t1.SourceID
ORDER BY SourceKey
FOR XML PATH('') ) D ( [Source Data] )

I get the results:

Source Data
BOOKING [2000182877] Test blabla
BOOKING [2000182878] Testabc1111


which is a start but how do I retrieve the above values and list them (along with other variables) in a varchar, like this:
Declare @TargetData varchar(max)
Set @TargetData = "Target Project Name"
Set @msg= 'Are you sure you wish to copy the following records to: ' + @TargetData + '?' +
'BOOKING [2000182877] Test blabla'
'BOOKING [2000182878] Testabc1111'

I would be very grateful if you could offer some more assistance!
Thanks,
Winnie
Post #1474545
Posted Wednesday, July 17, 2013 7:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
can you please provide some sample data with resultant output.......


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1474584
Posted Wednesday, July 17, 2013 8:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 3,329, Visits: 7,184
You might want to try something like this
Declare @msg varchar(8000),
@TargetData varchar(max)
Set @TargetData = "Target Project Name"
Set @msg= 'Are you sure you wish to copy the following records to: ' + @TargetData + '?'

SELECT @msg = @msg + CHAR(10) + SourceKey + ' [' + SourceID +'] '+ SourceComment
FROM (
SELECT DISTINCT SourceKey, SourceID, SourceComment
FROM #TempSourceOutput t2) t
ORDER BY SourceKey




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1474664
Posted Monday, July 22, 2013 1:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 12, 2013 6:35 AM
Points: 4, Visits: 11
Thank you both kapil_kk & Luis Cazares,
I'm sorry I have been unexpectedly offline for a few days so apologies for the late reply.

I managed in the end and it wasn't too difficult either.

Here is what I did:

I populated the table like so:

--Concatenate source data values and insert into temp table, seperated by a $ sign
Insert Into #TempSourceOutput (All_Source_Data)
Select @SourceKey + ' [' + Cast(@SourceID as varchar) +'] '+ @SourceComment + '$'

Then I got each row from temp table #TempSourceOutput and placed it in one single row of data for use in message later

Declare @Txt1 varchar(MAX)
Declare @AllSourceData varchar (max)
Set @Txt1=''

Select @Txt1 = @Txt1 + All_Source_Data
From #TempSourceOutput
Select @AllSourceData = LEFT(@Txt1,LEN(@Txt1)-1)

Then I used the @AllSourceData variable in my message:

Set @msg= 'Are you sure you wish to copy ' + CAST(@NumberOfTransfers as varchar) + ' Objects to ' + @TargetKey + ' [' +
CAST(@TargetID as varchar) + '] ' + @TargetComment + '?' + CHAR(10)+CHAR(13) + Replace(@AllSourceData,'$',+
CHAR(10)+CHAR(13))

I'm sure it's not the most sophisticated of solutions, but it works for me.
Many thanks for your help.
Winnie
Post #1475887
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse