Combine multiple lines for one record into one comma-delimted field

Here is a query I have: It's long, so bear with me.
Scroll to the bottom to see what question I have, please.
This is a SQL 2003 db. In the select statement I am only asking for 2
fields which in reality, I am asking for about 40 (hence the long FROM
section)
SELECT v_basic_booking_data_ODBC.ptt_last_name,
dbo.v_basic_case_rec_data.cr_urn, dbo.v_CRA_cpt_code.cpt_code
FROM dbo.v_basic_case_rec_data INNER JOIN
dbo.v_CRA_BO_01_Journaling_Data
v_CRA_BO_01_Journaling_Data ON
dbo.v_basic_case_rec_data.cr_urn =
v_CRA_BO_01_Journaling_Data.cr_urn LEFT OUTER JOIN
dbo.v_CRA_cpt_code ON
dbo.v_basic_case_rec_data.cr_urn = dbo.v_CRA_cpt_code.cr_urn RIGHT OUTER
JOIN
reportuser.v_BKA_02_Booking_Data
v_BKA_02_Booking_Data INNER JOIN
dbo.v_basic_booking_data_ODBC
v_basic_booking_data_ODBC ON
v_BKA_02_Booking_Data.book_urn =
v_basic_booking_data_ODBC.book_urn INNER JOIN
dbo.v_Surg_Proc_BK_ODBC v_Surg_Proc_BK_ODBC ON
v_BKA_02_Booking_Data.book_urn = v_Surg_Proc_BK_ODBC.book_urn INNER JOIN
dbo.v_PTA_01_Patient_Data v_PTA_01_Patient_Data ON
v_basic_booking_data_ODBC.ptt_urn = v_PTA_01_Patient_Data.ptt_urn INNER
JOIN
dbo.book_audref book_audref ON
v_basic_booking_data_ODBC.book_urn = book_audref.or_book_urn ON
v_CRA_BO_01_Journaling_Data.cr_urn =
book_audref.cr_urn
WHERE (v_basic_booking_data_ODBC.book_date >= CONVERT(DATETIME,
'2006-03-10 00:00:00', 102)) AND
(v_basic_booking_data_ODBC.book_date <
CONVERT(DATETIME, '2006-03-16 00:00:00', 102))
ORDER BY v_basic_booking_data_ODBC.room_descr,
v_basic_booking_data_ODBC.book_date, v_basic_booking_data_ODBC.room_mnc
I get results like
lname cptcode
DOE 1111111
DOE 4343445
SMITH 5456544
RALF 4789008
what I want is for Doe's line to read
DOE 111111,4343445
How can I do this with my extensive query above?
Thank you.
*** Sent via Developersdex http://www.examnotes.net ***Joe , since you have mot posetd DDL+ sample data I did some testing on my
own. Just be aware that the below approach isn't reliable and shoud be
avoided ,instead do such reports on the client side
create table w
(
id int not null,
t varchar(50) not null
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"Joey Martin" <joey@.kytechs.com> wrote in message
news:uwKf88$UGHA.2444@.TK2MSFTNGP14.phx.gbl...
> Here is a query I have: It's long, so bear with me.
> Scroll to the bottom to see what question I have, please.
> This is a SQL 2003 db. In the select statement I am only asking for 2
> fields which in reality, I am asking for about 40 (hence the long FROM
> section)
> SELECT v_basic_booking_data_ODBC.ptt_last_name,
> dbo.v_basic_case_rec_data.cr_urn, dbo.v_CRA_cpt_code.cpt_code
> FROM dbo.v_basic_case_rec_data INNER JOIN
> dbo.v_CRA_BO_01_Journaling_Data
> v_CRA_BO_01_Journaling_Data ON
> dbo.v_basic_case_rec_data.cr_urn =
> v_CRA_BO_01_Journaling_Data.cr_urn LEFT OUTER JOIN
> dbo.v_CRA_cpt_code ON
> dbo.v_basic_case_rec_data.cr_urn = dbo.v_CRA_cpt_code.cr_urn RIGHT OUTER
> JOIN
> reportuser.v_BKA_02_Booking_Data
> v_BKA_02_Booking_Data INNER JOIN
> dbo.v_basic_booking_data_ODBC
> v_basic_booking_data_ODBC ON
> v_BKA_02_Booking_Data.book_urn =
> v_basic_booking_data_ODBC.book_urn INNER JOIN
> dbo.v_Surg_Proc_BK_ODBC v_Surg_Proc_BK_ODBC ON
> v_BKA_02_Booking_Data.book_urn = v_Surg_Proc_BK_ODBC.book_urn INNER JOIN
> dbo.v_PTA_01_Patient_Data v_PTA_01_Patient_Data ON
> v_basic_booking_data_ODBC.ptt_urn = v_PTA_01_Patient_Data.ptt_urn INNER
> JOIN
> dbo.book_audref book_audref ON
> v_basic_booking_data_ODBC.book_urn = book_audref.or_book_urn ON
> v_CRA_BO_01_Journaling_Data.cr_urn =
> book_audref.cr_urn
> WHERE (v_basic_booking_data_ODBC.book_date >= CONVERT(DATETIME,
> '2006-03-10 00:00:00', 102)) AND
> (v_basic_booking_data_ODBC.book_date <
> CONVERT(DATETIME, '2006-03-16 00:00:00', 102))
> ORDER BY v_basic_booking_data_ODBC.room_descr,
> v_basic_booking_data_ODBC.book_date, v_basic_booking_data_ODBC.room_mnc
>
> I get results like
> lname cptcode
> DOE 1111111
> DOE 4343445
> SMITH 5456544
> RALF 4789008
> what I want is for Doe's line to read
> DOE 111111,4343445
> How can I do this with my extensive query above?
> Thank you.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||In my experience of trying it, dynamic columns in SQL is horrible, and
I've only achieved it with some messy Dynamic SQL. What are you
outputting to? if it's going out to a web app or something then I'd
suggest using the c# (or whatever language) to clean up your result set
there. SQL is more geared to obtaining rather than formatting data.
Cheers
Will|||http://www.aspfaq.com/2529
"Joey Martin" <joey@.kytechs.com> wrote in message
news:uwKf88$UGHA.2444@.TK2MSFTNGP14.phx.gbl...
> Here is a query I have: It's long, so bear with me.
> Scroll to the bottom to see what question I have, please.
> This is a SQL 2003 db. In the select statement I am only asking for 2
> fields which in reality, I am asking for about 40 (hence the long FROM
> section)
> SELECT v_basic_booking_data_ODBC.ptt_last_name,
> dbo.v_basic_case_rec_data.cr_urn, dbo.v_CRA_cpt_code.cpt_code
> FROM dbo.v_basic_case_rec_data INNER JOIN
> dbo.v_CRA_BO_01_Journaling_Data
> v_CRA_BO_01_Journaling_Data ON
> dbo.v_basic_case_rec_data.cr_urn =
> v_CRA_BO_01_Journaling_Data.cr_urn LEFT OUTER JOIN
> dbo.v_CRA_cpt_code ON
> dbo.v_basic_case_rec_data.cr_urn = dbo.v_CRA_cpt_code.cr_urn RIGHT OUTER
> JOIN
> reportuser.v_BKA_02_Booking_Data
> v_BKA_02_Booking_Data INNER JOIN
> dbo.v_basic_booking_data_ODBC
> v_basic_booking_data_ODBC ON
> v_BKA_02_Booking_Data.book_urn =
> v_basic_booking_data_ODBC.book_urn INNER JOIN
> dbo.v_Surg_Proc_BK_ODBC v_Surg_Proc_BK_ODBC ON
> v_BKA_02_Booking_Data.book_urn = v_Surg_Proc_BK_ODBC.book_urn INNER JOIN
> dbo.v_PTA_01_Patient_Data v_PTA_01_Patient_Data ON
> v_basic_booking_data_ODBC.ptt_urn = v_PTA_01_Patient_Data.ptt_urn INNER
> JOIN
> dbo.book_audref book_audref ON
> v_basic_booking_data_ODBC.book_urn = book_audref.or_book_urn ON
> v_CRA_BO_01_Journaling_Data.cr_urn =
> book_audref.cr_urn
> WHERE (v_basic_booking_data_ODBC.book_date >= CONVERT(DATETIME,
> '2006-03-10 00:00:00', 102)) AND
> (v_basic_booking_data_ODBC.book_date <
> CONVERT(DATETIME, '2006-03-16 00:00:00', 102))
> ORDER BY v_basic_booking_data_ODBC.room_descr,
> v_basic_booking_data_ODBC.book_date, v_basic_booking_data_ODBC.room_mnc
>
> I get results like
> lname cptcode
> DOE 1111111
> DOE 4343445
> SMITH 5456544
> RALF 4789008
> what I want is for Doe's line to read
> DOE 111111,4343445
> How can I do this with my extensive query above?
> Thank you.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

Column labels in a View

Please bear with me, as I do not work with SQL much. I have a view set
up that compiles rolling monthly data from a historical table. The
view currently looks like this:
Server Current Month One Month Ago Two Months Ago...
Server1 <data> <data> <data>
Is there any way to set the column label for the second column to
datename(m, getdate()) in place of "Current Month"? I would obviously
then want to apply the same logic to the remaining columns.
TIA,
Dave
Hi
You would need to drop the view and re-create it, although this is really a
UI issue!
John
"davrion@.hotmail.com" wrote:

> Please bear with me, as I do not work with SQL much. I have a view set
> up that compiles rolling monthly data from a historical table. The
> view currently looks like this:
> Server Current Month One Month Ago Two Months Ago...
> Server1 <data> <data> <data>
> Is there any way to set the column label for the second column to
> datename(m, getdate()) in place of "Current Month"? I would obviously
> then want to apply the same logic to the remaining columns.
> TIA,
> Dave
>

Column labels in a View

Please bear with me, as I do not work with SQL much. I have a view set
up that compiles rolling monthly data from a historical table. The
view currently looks like this:
Server Current Month One Month Ago Two Months Ago...
Server1 <data> <data> <data>
Is there any way to set the column label for the second column to
datename(m, getdate()) in place of "Current Month"? I would obviously
then want to apply the same logic to the remaining columns.
TIA,
DaveHi
You would need to drop the view and re-create it, although this is really a
UI issue!
John
"davrion@.hotmail.com" wrote:

> Please bear with me, as I do not work with SQL much. I have a view set
> up that compiles rolling monthly data from a historical table. The
> view currently looks like this:
> Server Current Month One Month Ago Two Months Ago...
> Server1 <data> <data> <data>
> Is there any way to set the column label for the second column to
> datename(m, getdate()) in place of "Current Month"? I would obviously
> then want to apply the same logic to the remaining columns.
> TIA,
> Dave
>

Column labels in a View

Please bear with me, as I do not work with SQL much. I have a view set
up that compiles rolling monthly data from a historical table. The
view currently looks like this:
Server Current Month One Month Ago Two Months Ago...
Server1 <data> <data> <data>
Is there any way to set the column label for the second column to
datename(m, getdate()) in place of "Current Month"? I would obviously
then want to apply the same logic to the remaining columns.
TIA,
DaveHi
You would need to drop the view and re-create it, although this is really a
UI issue!
John
"davrion@.hotmail.com" wrote:
> Please bear with me, as I do not work with SQL much. I have a view set
> up that compiles rolling monthly data from a historical table. The
> view currently looks like this:
> Server Current Month One Month Ago Two Months Ago...
> Server1 <data> <data> <data>
> Is there any way to set the column label for the second column to
> datename(m, getdate()) in place of "Current Month"? I would obviously
> then want to apply the same logic to the remaining columns.
> TIA,
> Dave
>