June 20, 2012 at 6:06 am
ChrisM@Work (6/20/2012)
pwalter83 (6/20/2012)
ChrisM@Work (6/20/2012)
Are you seeing one row per BOOKING_ID in your output?- here's where adequate sample data would have helped π
yeah, I get only one row per BOOKING_ID and that is what I want. somehow it does'nt work for GEN_COMMOD_CD column. would I have to tweak the FOR XML PATH syntax ?
Per your solution, I was wondering if the 2 values were identical, would it still show up as 2 different values separated by comma or would it just show a single value (representing both) ?
I asked because in the GEN_COMMOD_CD column the values are identical (Loudspeakers), so I thought if it would show up as Loudspeakers instead of - Loudspeakers, Loudspeakers
Are you absolutely sure there are two rows in table GEN_COMMOD_CD for the booking ID you're testing with? Check by doing a select against GEN_COMMOD_CD alone.
Sorry for wasting your time. You are right, there is only a single entry for this column. I need to wake up.
Thanks for your immense help. I think its done now.
June 20, 2012 at 6:24 am
ChrisM@Work (6/20/2012)
pwalter83 (6/20/2012)
ChrisM@Work (6/20/2012)
Are you seeing one row per BOOKING_ID in your output?- here's where adequate sample data would have helped π
yeah, I get only one row per BOOKING_ID and that is what I want. somehow it does'nt work for GEN_COMMOD_CD column. would I have to tweak the FOR XML PATH syntax ?
Per your solution, I was wondering if the 2 values were identical, would it still show up as 2 different values separated by comma or would it just show a single value (representing both) ?
I asked because in the GEN_COMMOD_CD column the values are identical (Loudspeakers), so I thought if it would show up as Loudspeakers instead of - Loudspeakers, Loudspeakers
Are you absolutely sure there are two rows in table GEN_COMMOD_CD for the booking ID you're testing with? Check by doing a select against GEN_COMMOD_CD alone.
Just one thing Chris, what would I have to do if I need to display a column from the table that has been used to display concatenated values ?
For e.g. I need to display another column REEFER_FLG without any concatenation that is in the GENERAL_COMMODITY_CD table
-----------------------------------------------------------------------------------
CROSS APPLY (
SELECT
=
STUFF(
(SELECT ', ' + mbc.GENERAL_COMMODITY_CD
FROM MG_BOOKING_COMMODITY mbc
WHERE mbc.BOOKING_ID = mb.BOOKING_ID
--AND mber.BOOKING_NUM = mb.BOOKING_NUM AND mber.TRADE_CD = mb.TRADE_CD
--ORDER BY GENERAL_COMMODITY_CD
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,2,'')
) b (GEN_COMMOD_CD)
June 20, 2012 at 6:35 am
Since GENERAL_COMMODITY_CD table has a different cardinality to your result set, having multiple rows per BOOKING_ID, you will need to preaggregate it, either as a CROSS APPLY or as a derived table. Have a go, if you get stuck, repost.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 20, 2012 at 7:44 am
ChrisM@Work (6/20/2012)
Since GENERAL_COMMODITY_CD table has a different cardinality to your result set, having multiple rows per BOOKING_ID, you will need to preaggregate it, either as a CROSS APPLY or as a derived table. Have a go, if you get stuck, repost.
I used the Cross apply technique as you mentioned and it worked. Thanks again !
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply