Is it possible to combine the values from more than 1 record into 1 resultset?

Question

Is it possible to combine the values from more than 1 record into 1 resultset?

Rod at work

SSC-Dedicated

Points: 34008
More actions
August 20, 2010 at 2:03 pm

#144774

I'm working on a report, build on a stored procedure, that is supposed to only return 1 resultset. I needed to add a field from a table (called Appointments) that hadn't previously been added to the stored proc. I confess I made a simplistic assumption (and yes, I know the old adage about what happens when someone assumes something), that there would only be 1 record in the Appointments table, to the stored proc. Well, after testing my report for a while I came across some data that proved my assmption to be wrong. But man, changing that report around would be kind of a huge pain. So I'm wondering if it is possible to have SQL SELECT statement, which would combine, as in contatenate, all of the values into 1 returned value. Here is the Appointments table, with all columns removed but the columns I need to explain what I'm talking about:
CREATE TABLE Appointments(
AppointmentID int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
ClientNumber int NULL,
CaseNumber smallint NULL,
Comments varchar(4000) NULL,
CONSTRAINT PK_Appointments PRIMARY KEY CLUSTERED
(
AppointmentID ASC
)
What I'm doing in the stored proc is matching against the Appointments table on ClientNumber and CaseNumber. The only field I'm returning from the Appointments table is the Comments field. In my testing I've found 2 records in the Appointments table for the same client number and case number, but obviously different AppointmentID's. Is it possible, in a SQL SELECT statement, of concatenating each of the Comments in both records, into just 1 resultset? Thus, if record with AppointmentID of 4 had a Comments of "This is a test.", and another record with AppointmentID of 7 had a Comments field of "This is the end of the test.", and both records had the same client/case numbers, is it possible to have a SELECT statement that would combine them to form: "This is a test.This is the end of the test."?
Kindest Regards, Rod Connect with me on LinkedIn.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Ok, you did the first stepby providing the DDL script for the table.

The second step would be to provide some ready to use (fake) sample data (provided as INSERT statements). And the final step would be to show us what your expected output would look like (most excellent would be some sort of SELECT ... UNION ALL ... without a table referenced) so we can actually see if you'd like to have the results in one or moer columns.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

pankushmehta Ten Centuries Points: 1217 More actions · Answer 2

try using CTE.

you can also use cursor(smartly) or a while loop for all the duplicate appointmentids..

Mike01 SSChampion Points: 11395 More actions · Answer 3

try this

declare @a TABLE (

AppointmentID int IDENTITY(1,1) NOT NULL,

ClientNumber int NULL,

CaseNumber smallint NULL,

Comments varchar(40) NULL)

declare @C TABLE (

ClientNumber int Not NULL,

CaseNumber smallint Not NULL)

insert into @a

select 1, 1, 'Comment1 for Client1' union all

select 1, 1, 'Comment2 for Client1' union all

select 1, 2, 'Comment1 for Client1 Case 2' union all

select 2, 1, 'Comment1 for Client2' union all

select 2, 2, 'Comment1 for Client2 Case 2' union all

select 3, 1, 'Comment1 for Client3' union all

select 3, 1, 'Comment2 for Client3' union all

select 3, 1, 'Comment3 for Client3' union all

select 3, 1, 'Comment4 for Client3'

insert into @C

select 1, 1 union all

select 1, 2 union all

select 2, 1 union all

select 1, 2 union all

select 3, 1

SELECT ClientNumber, CaseNumber,

STUFF(

( SELECT '-' + Comments

FROM @a a

WHERE a.ClientNumber = c.ClientNumber

and a.CaseNumber = c.CaseNumber

FOR XML PATH('')

),

1,

1,''

) AS t

FROM @a c

GROUP BY ClientNumber, CaseNumber

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 4

I don't understand everything you've done in the SELECT statement, Mike01, but it looks like that will work. Thank you!

Kindest Regards, Rod Connect with me on LinkedIn.

nigel. SSChampion Points: 11636 More actions · Answer 5

Rod at work (8/23/2010)
I don't understand everything you've done in the SELECT statement, Mike01, but it looks like that will work. Thank you!

Then have a read of this[/url]

Nigel
How to post questions

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 6

Thank you, nigel!

Kindest Regards, Rod Connect with me on LinkedIn.

WayneS SSC Guru Points: 95460 More actions · Answer 7

Rod at work (8/23/2010)
I don't understand everything you've done in the SELECT statement, Mike01, but it looks like that will work. Thank you!

Here's my two cents ... Until you do understand it, DO NOT use it in a production system.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2