Need help with a SELECT statement.

Hi,

Give the info below.  How can I concate multiple rows into single.  Please 
see the desired results below.
Thanks so much in advance.

IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
  DROP TABLE #Temp 
GO

CREATE TABLE #Temp
(
	[LoanNum] [varchar](8) NOT NULL,
	[ContactName] [varchar](50) NULL,
	[BelongsTo] [varchar](35) NULL,
	[ContactPhone] [varchar](15) NULL
);
go


INSERT INTO #Temp ([LoanNum],[ContactName],[BelongsTo],[ContactPhone])VALUES 
('104402', 'Annie Rylke', 'Realtor', '239-810-3254')
INSERT INTO #Temp ([LoanNum],[ContactName],[BelongsTo],[ContactPhone])VALUES 
('104402', 'Paul Bernard', 'Borrower', '(713) 820-2888')
go

    SELECT *
      FROM #Temp;
    go
    
LoanNum  ContactName                                        BelongsTo        
                   ContactPhone
-------- -------------------------------------------------- 
----------------------------------- ---------------
104402   Annie Rylke                                        Realtor          
                   239-810-3254
104402   Paul Bernard                                       Borrower         
                   (713) 820-2888    


-- Desire results:
 LoanNum   ContactInfo
 -------   ------------
 104402    Annie Rylke, Realtor, 239-810-3254 | Paul Bernard, Borrower, 
(713) 820-2888
 




-- Testing...
SELECT LoanNum, 
	       CAST(STUFF((SELECT TOP 100 PERCENT ', ' + x.ContactPhone
	                     FROM #Temp x 
	                    WHERE x.LoanNum 	= y.LoanNum 
                         ORDER BY ',' + x.ContactPhone FOR XML PATH('')), 1, 
2, '')  AS VARCHAR(50)) AS 'ContactPhone'
     FROM #Temp AS y
 GROUP BY LoanNum;
0
Utf
3/23/2010 9:58:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
808 Views

Similar Articles

[PageSpeed] 4

Try this:

SELECT LoanNum,
        STUFF((SELECT ' | ' + x.ContactName + ', ' +
                              x.BelongsTo + ', ' +
                              x.ContactPhone
	          FROM #Temp AS x
               WHERE x.LoanNum = y.LoanNum
               FOR XML PATH('')), 1, 3, '') AS ContactInfo
FROM #Temp AS y
GROUP BY LoanNum;

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
3/23/2010 10:06:09 PM
On Mar 23, 5:58=A0pm, LN <L...@discussions.microsoft.com> wrote:
> Hi,
>
> Give the info below. =A0How can I concate multiple rows into single. =A0P=
lease
> see the desired results below.
> Thanks so much in advance.
>
> IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
> =A0 DROP TABLE #Temp
> GO
>
> CREATE TABLE #Temp
> (
> =A0 =A0 =A0 =A0 [LoanNum] [varchar](8) NOT NULL,
> =A0 =A0 =A0 =A0 [ContactName] [varchar](50) NULL,
> =A0 =A0 =A0 =A0 [BelongsTo] [varchar](35) NULL,
> =A0 =A0 =A0 =A0 [ContactPhone] [varchar](15) NULL
> );
> go
>
> INSERT INTO #Temp ([LoanNum],[ContactName],[BelongsTo],[ContactPhone])VAL=
UES
> ('104402', 'Annie Rylke', 'Realtor', '239-810-3254')
> INSERT INTO #Temp ([LoanNum],[ContactName],[BelongsTo],[ContactPhone])VAL=
UES
> ('104402', 'Paul Bernard', 'Borrower', '(713) 820-2888')
> go
>
> =A0 =A0 SELECT *
> =A0 =A0 =A0 FROM #Temp;
> =A0 =A0 go
>
> LoanNum =A0ContactName =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0BelongsTo =A0 =A0 =A0 =A0
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ContactPhone
> -------- --------------------------------------------------
> ----------------------------------- ---------------
> 104402 =A0 Annie Rylke =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Realtor =A0 =A0 =A0 =A0 =A0
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0239-810-3254
> 104402 =A0 Paul Bernard =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 Borrower =A0 =A0 =A0 =A0
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0(713) 820-2888 =A0 =A0
>
> -- Desire results:
> =A0LoanNum =A0 ContactInfo
> =A0------- =A0 ------------
> =A0104402 =A0 =A0Annie Rylke, Realtor, 239-810-3254 | Paul Bernard, Borro=
wer,
> (713) 820-2888
>
> -- Testing...
> SELECT LoanNum,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0CAST(STUFF((SELECT TOP 100 PERCENT ', ' + =
x.ContactPhone
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM #Temp x
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 WHERE x.LoanNum =
=A0 =A0 =3D y.LoanNum
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ORDER BY ',' + x.Conta=
ctPhone FOR XML PATH('')), 1,
> 2, '') =A0AS VARCHAR(50)) AS 'ContactPhone'
> =A0 =A0 =A0FROM #Temp AS y
> =A0GROUP BY LoanNum;


SELECT	 BeginRow.LoanNum				LoanNum
		,BeginRow.ContactName			Realtor
		,BeginRow.BelongsTo				RealtorName
		,BeginRow.ContactPhone			RealtorPhone
		,SecondRow.ContactName			BorrowerName
		,SecondRow.BelongsTo			Borrower
		,SecondRow.ContactPhone			BorrowerPhone
FROM #Temp			BeginRow
LEFT JOIN #Temp		SecondRow	ON	BeginRow.LoanNum =3D SecondRow.LoanNum
								AND SecondRow.BelongsTo =3D 'Borrower'
WHERE BeginRow.BelongsTo =3D 'Realtor';


0
Tom
3/23/2010 11:01:22 PM
"LN" <LN@discussions.microsoft.com> wrote in message 
news:DA2E93E1-76CB-47BD-9827-942DF816EE7A@microsoft.com...
> Hi,
>
> Give the info below.  How can I concate multiple rows into single.  Please
> see the desired results below.
> Thanks so much in advance.
>
> IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
>  DROP TABLE #Temp
> GO
>
> CREATE TABLE #Temp
> (
> [LoanNum] [varchar](8) NOT NULL,
> [ContactName] [varchar](50) NULL,
> [BelongsTo] [varchar](35) NULL,
> [ContactPhone] [varchar](15) NULL
> );
> go
>
>
> INSERT INTO #Temp 
> ([LoanNum],[ContactName],[BelongsTo],[ContactPhone])VALUES
> ('104402', 'Annie Rylke', 'Realtor', '239-810-3254')
> INSERT INTO #Temp 
> ([LoanNum],[ContactName],[BelongsTo],[ContactPhone])VALUES
> ('104402', 'Paul Bernard', 'Borrower', '(713) 820-2888')
> go
>
>    SELECT *
>      FROM #Temp;
>    go
>
> LoanNum  ContactName                                        BelongsTo
>                   ContactPhone
> -------- -------------------------------------------------- 
> ----------------------------------- ---------------
> 104402   Annie Rylke                                        Realtor
>                   239-810-3254
> 104402   Paul Bernard                                       Borrower
>                   (713) 820-2888
>
>
> -- Desire results:
> LoanNum   ContactInfo
> -------   ------------
> 104402    Annie Rylke, Realtor, 239-810-3254 | Paul Bernard, Borrower,
> (713) 820-2888
>

Looks like you need to normalize your table...move the borrowers into a 
separate table, indexed by LoanNum.
Then do a join in the query and return a result set from both tables. 

0
Edward
3/23/2010 11:47:23 PM
Thanks so much Plamen and Tom.  Now, I try to understand your query Plamen.  
Again, I appreciate all your help.

L

"Plamen Ratchev" wrote:

> Try this:
> 
> SELECT LoanNum,
>         STUFF((SELECT ' | ' + x.ContactName + ', ' +
>                               x.BelongsTo + ', ' +
>                               x.ContactPhone
> 	          FROM #Temp AS x
>                WHERE x.LoanNum = y.LoanNum
>                FOR XML PATH('')), 1, 3, '') AS ContactInfo
> FROM #Temp AS y
> GROUP BY LoanNum;
> 
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com
> .
> 
0
Utf
3/24/2010 3:40:01 AM
Reply:

Similar Artilces:

Calculate in SELECT
How do I get: SELECT ((127/153)*100) AS percentage to return 83 (or so)? Regards /Morten select cast((127*1.0/153*1.0) * 100 as int) as percentage DECLARE @Value DECIMAL(10,2) SET @Value = CONVERT(DECIMAL(10,2), 127) / CONVERT(DECIMAL(10,2), 153) * 100 SELECT @Value OR SELECT (127 * 100)/(153) -Eric Isaacs Add a .0 to the 127. SELECT ((127.0/153)*100) -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ---------------- "Snedker" <morten.snedker@gmail.com&...

Help with DLookup
I have a DLookup function on my form and it works by finding the information once I exit and then re-enter the form, it does not bring in the information as soon as I enter the information in the lookup field. =DLookUp("[Employee Name]","[Employee Information]","[Employee ID]=forms![Timesheet Entry]![Employee ID]") Does anybody know why it is not updating immediately? All help is greatly appreciated Where are you using this expression -- as the control source of a textbox? More details about the form's design and how you're using the form, please....

Conditional Formating Help
Hi I wonder if anyone could help me, I am after code for the following. cell e6 = Keys Sent Column f6 = Keys due Column g6 = Keys received What I want the script to do is if there is no value in g6 and todays date is greater then the date stated in F6, then the cell turns red (prompt to inform me that keys are late). Many thanks Dan Use a CF formula of =AND(G6="",TODAY()>F6) HTH Bob "housinglad" <housinglad@discussions.microsoft.com> wrote in message news:A5887369-33DA-489A-BEC9-8173707313C6@microsoft.com... > Hi > ...

Tricky Formula.. Please Help
I have created a time roster.. easy in each day I have start,end and break deduction.. still no probs I need to create an output formula for hours worked after a 16:00 from the start and end range on a day. So in the roster it will still display normal hours, however I will create a field for hours after 16:00. this is for calculating a different wage rate. Thanks Aaron See response in .programming -- HTH RP (remove nothere from the email address if mailing direct) "Aaron H" <aaron@istarnetworks.com.au> wrote in message news:uU96kXI$EHA.2076@TK2MSFTNGP15.phx.gbl... ...

HELP- error Outlook
What's this mean and how to fix? in error dialogue box: "c:\program~1\micro~2 \office\outlook.exe" Abnormal program termination .. ...

help diagnosing what's going on in ExchangeServer2000
I'm fairly ignorant of exactly how ES2000 works and would like some general information to help in my diagnosing why some emails are not being delivered to certain email domains (more correctly, why some emails are not being forwarded actually...ES2K is part of a voicemail system where some users have ..wav copies of the voicemails forwarded to their various off-site email accounts) . Here are my questions: 1. How can I see what emails are being forward? Is there a file/folder akin to the SentItems folder in Outook? 2. If there are send failures, where can I find them and how can ...

Need a Pause in a macro
Hi, I have a macro that goes out and retrieves data from a text file opened with Excel, then pastes the data back in my worksheet and closes the text file back up. For some reason, the data that is being brought in is being inserted in multiple columns because it is using a space seperator now in lieu of a tab seperator. Anyhow, if I go into my macro and remove the part that closes the file I'm retrieving the data from everything still works??? However, that file won't close. Is there a line that I can add to my macro to allow a pause for things to get caught up before the workboo...

Noob Question For Selecting Multiple Fields On A Form
Hello... In versions earlier than 2007 I would be able to go to the Menu Bar and click Edit -> Select All to select all fields on the form. Now, my question is... where in 2007 did the put that functionality? If Microsoft removed it from there... where did they recreate it? Thanks! Squirrel "SQLSQUIRREL" <SQLSQUIRREL@discussions.microsoft.com> wrote in message news:333547A1-9C6A-422B-9CD5-97D79D6037DF@microsoft.com... > Hello... > > In versions earlier than 2007 I would be able to go to the Menu Bar and > click Edit -> Select All to se...

Urgent Question Please Help: Credit Card Setup Problem
We have an urgent question regarding credit card setup/cash receipts/bank rec We understand that in Great Plains when setting up a Credit Card to be used by a Customer, it must be setup as a "Bank Card", not a "Charge Card", in order for the transaction to hit Bank Rec. However, we mistakenly setup all the Credit Cards as "Charge Cards", therefore after posting Credit Card Cash Receipt transactions in Receivables, the transaction does not appear in Bank Rec for us to perform Bank Deposits. Please Please Help!!! Is there any way to "fix" those pos...

Conditional Statement in a Macro
I have a time sheet program with a pivot table on each sheet to sum the hours worked on each job #. Each pivot table is named pivot table 7. I have set up a macro to update the pivot table easily. It is as follows: Sub Update_Pivot_Table() ' ' Update_Pivot_Table Macro ' Keyboard Shortcut: Ctrl+p ' Range("E63").Select ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh Range("H62").Select End Sub Thus, since each table is named "PivotTable7" the macro works on any of the sheets. I have added a pivot tab...

Select non-consecutive rows
Hi, Is there a way to select non-consecutive rows besides Ctrl + click. If I want to insert a blank row above about 40 rows, this can become quite tedious. -- Thanks! Dee Hi only possible with VBA in this case -- Regards Frank Kabel Frankfurt, Germany "dee" <dee@discussions.microsoft.com> schrieb im Newsbeitrag news:23B3F46E-C956-4FCC-9880-08B9A200B3A2@microsoft.com... > Hi, > > Is there a way to select non-consecutive rows besides Ctrl + click. If I > want to insert a blank row above about 40 rows, this can become quite tedious. > > > -- > Tha...

help with unusual request
Hello everyone, My boss has hit me with an odd request and I would like to ask for someone to point me in the right direction. What he wants is everytime anyone sends or recieves an email that a copy is made that he can get to anytime he wants. I talked to him about our daily backups, but he wants something that's realtime and that he can get to whenever he wants. I'm stuck and looking for ideas. We're currently on Exchange 5.5 with all of the latest service packs and patches. Any and all help is very much appreciated. You can enable "message journaling" fea...

Help designing a checklist in access
Hello All My company currently uses excel checklists to ensure that projects are completed fully. we have around 10 different checklists. each checklist (excel file) has 6 sheets, each sheet 15 questions or steps. most answers are Y,N,NA and the employees put their initials in the correct answer column. Many questions are redundant throughout the 10 checklists. I would like to create the checklists in access because their are too many excel files getting lost and if we change question/ step. we have to change it multiple times. the employees need to enter a clients name and then choose the c...

Why are checks printing incorrect amounts? Please help.
When I print a Payables check batch, several invoices within the batch are printing incorrectly as $0.00 amount, even though it should be paying off a certain amount. The problem seems to be with invoices that are applied to credit memos. Say I have an invoice for $500 and I apply a credit memo of $80 to the invoice, when I go back to pay the invoice, it should pay $420, but instead it shows $0.00 under amount paid column of check stub. Does anyone know why this is occuring? By the way, I am using MICR check printing. ...

Timesheet Pseudo Tied Mode
I'm hoping that I might have a colleague out there who could help me offline figure out how to install this open source utility (unsupported by Microsoft but apparently works OK for some of their clients) that streamlines the Timesheet process (automatically synchronizes the data into My Tasks whenever a timesheet is saved). I'm not sure where to start and may need someone to bounce questions off of - especially if you've been successful with it. Thanks, Andy Novak UNT Hi Anovak, We installed it (still using the version 1.3 based on event handler) and we're usi...

Please help with Exchange 2003 SMTP Connector
I'm trying to create an SMTP Connector in Exchange Server 2003, to have our company's email sent out (Internet/External emails) to a specific mail forwarder server (the "specific" server is our ISP/Firewall provider server). But I'm having problems understanding how to set this up properly with our Exchange 2003 Server, I know I have to create a new SMTP Connector but not sure what settings to make. Could someone please help me with this. Thanks William On Thu, 25 Feb 2010 22:41:39 -0500, "Bill" <wstyler@verizon.net> wrote: >I&...

Landscape selected but printout persists in portrait. How to fix?
Occasionally, I have had a spreadsheet I created in landscape layout revert somehow to portrait. It still indicates that it is in landscape in the page setup menu but it print previews and prints in poitrait. I've tried copying the page to a new workbook, but it takes the portrait layout with it. Short of manually moving all my data to a new worksheet, what can I do to get back my landscape layout? excel page setup settings are different than your printer settings. sometimes one will override the other. i'd check them both. hth susan On Dec 2, 1:22=A0pm, R.Lamki...

Help in turning off underlining and blue font
Hello, Does anyone know if there is a way to turn off the automatic underlining and blue font of the text when entering a email address in a cell? Thanks, Bob Tools / Autocorrect options / Autoformat Tab / Replace Network paths with hyperlinks - Deselect it -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ----------------------------------------------------------------------...

Help!!Getting audio files to play continuously during slide show?!
I have Powerpoint 2002, and i am having a hard time getting the music file to play continuously during the slide show. it plays on the first slide (which is the only slide that has the little speaker picture on it), but then it doesn't transition to the next slide until after the song is done, when i want the song to play while the slides are transitioning. HELP please! Mega, Have a look here and see if it helps, there are tuturials in video demonstrating the process. http://www.pptfaq.com/FAQ00047.htm -- Luc Sanders MVP - PowerPoint "MegaB86" <MegaB86...

Help with counting formula
Sheet 1, Row 1, Column 2 - opening hours divide into 15min segments. B1 - 08:00, C2 - 08:15, D2 - 08:30, and so on. Sheet 2 , Columns 1 and 2 - a list of each agents break times. Column A - Start time (eg 10:00) and Column B - End time (eg 10:30). Sheet 1, Row 2, Column 1 - summary titles. (eg Cell A2 - "Morning Break"). Problem: I need to summarise the number of agents away on, say, "Morning Break" per 15 min segment. So, B2 must count the number of people on "Morning Break" at 08:00, B3..the number of people of people on morning break at 08:15, a...

Please help! Outlook not working at all!
Hi! I have been working with Outlook for years - it was always a great feature - but now it is critical since I have an Ipaq that I need to sync to it. Outlook somehow is no longer working/on my computer. I have tried repeatedly to reinstall - to "fix" - anything with NO luck. I get an icon to click on for Outlook and then it goes directly to personal folders (nothing actually happens). Every other component of Office 2000 is working perfectly. Any help would be GREATLY appreciated! I am desperado!!!! Have you run "Detect and Repair" from under help in Outl...

Match() - Offset()
Often I see mention of Offset(), Match(), & Index() XL functions. Could someone big-picture these functions for me? I have not used these functions yet in data lookup or evaluation. Shame on me. That said, The concept of "Index" I understand from db work. "Match" seems vaguely logical to me for "lookup" work. "Offset" means nothing to me yet. Reading the XL help or reference books do not give me typical uses and why's of the functions. My focus is the accounting/financial world. Using XP Pro & XL XP Pro SP1. Dennis This file b...

Reported error (0X800CC0F) the connection to the server was interrupted
I am receiving this error message on a client workstation, none of the other 4 users on the LAN are receiving this error. They are using Outlook 2002 connected through an ADSL modem. The user still can send and receive e-mails but this error message always appears. Any one have any ideas? Reported error (0X800CC0F) the connection to the server was interrupted Sean For those of you that have been getting "Sending and Receiving reported error .... connection to server was interrupted" as I have, try creating a new HTTP email account, send and receive a couple of messages from that...

How do I refresh the selection listboxes on a pivot table?
The field-level (column) selection boxes used to select the data to be included in a pivot table include old and new values. How do I refresh these list boxes so they only include values that are present in the current source data? The list box continues to show values that are no longer in the data set. Click in the Pivot Table to bring up the Pivot Table toolbar. (If that doesn't bring it up, rt-click a toolbar and select it from the list.) Click the button with the ! icon to Refresh the Pivot Table. Hope this helps |:>) "WWW.CS" wrote: > The field-level (colu...

Help with XslCompiledTransform
I am using XslCompiledTransform to transform XML into Excel 2003 SpreadsheetML. In order to get the resulting XML file to open in Excel, we need the following two lines at the top of the file: <?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> I can get the <?mso-application ...> line by using an <xsl:processing-instruction> element, but for the life of me I can't get XslCompiledTransform to include the basic <?xml...> line. When creating the XmlWriterSettings for use in the transform, I have not altered the OmitXmlDeclarati...