Need help with a SELECT statement.

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)

Need help with a SELECT statement.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-03-23 : 17:57:24

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;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-23 : 18:38:17

[code]
SELECT LoanNum,
CAST(STUFF((SELECT TOP 100 PERCENT '| ' + x.ContactName + ',' + x.BelongsTo + ',' + x.ContactPhone
FROM #Temp x
WHERE x.LoanNum = y.LoanNum
ORDER BY ',' + x.ContactName FOR XML PATH('')), 1, 2, '') AS VARCHAR(max)) AS 'ContactPhone'
FROM #Temp AS y
GROUP BY LoanNum;
[/code]

KH
[spoiler]Time is always against us[/spoiler]

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-03-23 : 23:44:30

Much appreciate for your help. Now, I try to understand your queries.

quote:

Originally posted by khtan


SELECT LoanNum,
CAST(STUFF((SELECT TOP 100 PERCENT '| ' + x.ContactName + ',' + x.BelongsTo + ',' + x.ContactPhone
FROM #Temp x
WHERE x.LoanNum = y.LoanNum
ORDER BY ',' + x.ContactName FOR XML PATH('')), 1, 2, '') AS VARCHAR(max)) AS 'ContactPhone'
FROM #Temp AS y
GROUP BY LoanNum;

KH
[spoiler]Time is always against us[/spoiler]

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-24 : 03:00:34

SELECT T.LoanNum,T1.BLOCK
FROM #Temp T
OUTER APPLY
(Select TOP 1 [BLOCK]= T.ContactName + ',' + T.BelongsTo + ',' + T.ContactPhone + '|'
FROM #Temp T1 WHERE T.LoanNum = T1.LoanNum) T1

THIS ALSO WORKS.

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-24 : 03:02:21

quote:
Originally posted by dineshrajan_it

SELECT T.LoanNum,T1.BLOCK
FROM #Temp T
OUTER APPLY
(Select TOP 1 [BLOCK]= T.ContactName + ',' + T.BelongsTo + ',' + T.ContactPhone + '|'
FROM #Temp T1 WHERE T.LoanNum = T1.LoanNum) T1

THIS ALSO WORKS.

Iam a slow walker but i never walk back

SORRY THIS IS WRONG.

Iam a slow walker but i never walk back

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-24 : 03:05:20

sorry you walk back...

Vaibhav T

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-24 : 03:15:50

quote:

Originally posted by khtan


SELECT LoanNum,
CAST(STUFF((SELECT TOP 100 PERCENT '| ' + x.ContactName + ',' + x.BelongsTo + ',' + x.ContactPhone
FROM #Temp x
WHERE x.LoanNum = y.LoanNum
ORDER BY ',' + x.ContactName FOR XML PATH('')), 1, 2, '') AS VARCHAR(max)) AS 'ContactPhone'
FROM #Temp AS y
GROUP BY LoanNum;

KH
[spoiler]Time is always against us[/spoiler]

I really could not understand your query can you explain what you are doing in this query or any link to make me understand.
thanks

Vaibhav T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources