June 19, 2012 at 10:09 am
Thanks for the ddl and sample data. What is the desired output from your sample data?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 10:11 am
pwalter83 (6/19/2012)
ChrisM@Work (6/19/2012)
Paul, how many rows max will you have to roll up like this? Your example shows two rows - can it be 3, or 4, or n?Could be up to 5 rows and may be even more ? but the above solution does't work as I get duplicate rows of data.
Something like this, you mean?
;WITH SampleData AS(
SELECT *
FROM (
SELECT 11515225, 'LEH100511', 'EURUSEC', '1 20D86,', 'LOUDSPEAKERS,' UNION ALL
SELECT 11515225, 'LEH100511', 'EURUSEC', '1 40D86,', 'MICROPHONES'
) d (BOOKING_ID, BOOKING_NUM, TRADE_CD, BOOKING_EQUIP, GEN_COMMOD_CD)
)
SELECT DISTINCT s.BOOKING_ID, s.BOOKING_NUM, s.TRADE_CD, a.BOOKING_EQUIP, b.GEN_COMMOD_CD
FROM SampleData s
CROSS APPLY (
SELECT
=
STUFF(
(SELECT ', ' + BOOKING_EQUIP
FROM SampleData
WHERE BOOKING_ID = s.BOOKING_ID AND BOOKING_NUM = s.BOOKING_NUM AND TRADE_CD = s.TRADE_CD
ORDER BY BOOKING_EQUIP
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,2,'')
) a (BOOKING_EQUIP)
CROSS APPLY (
SELECT
=
STUFF(
(SELECT ', ' + GEN_COMMOD_CD
FROM SampleData
WHERE BOOKING_ID = s.BOOKING_ID AND BOOKING_NUM = s.BOOKING_NUM AND TRADE_CD = s.TRADE_CD
ORDER BY GEN_COMMOD_CD
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,2,'')
) b (GEN_COMMOD_CD)
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 19, 2012 at 10:12 am
Based on the sample data, what is your expected results?
June 19, 2012 at 3:37 pm
the reason you get duplicates is because of your joins are returning courtesan product, can you just explain how you want your results 🙂
***The first step is always the hardest *******
June 20, 2012 at 1:58 am
ChrisM@Work (6/19/2012)
pwalter83 (6/19/2012)
ChrisM@Work (6/19/2012)
Paul, how many rows max will you have to roll up like this? Your example shows two rows - can it be 3, or 4, or n?Could be up to 5 rows and may be even more ? but the above solution does't work as I get duplicate rows of data.
Something like this, you mean?
;WITH SampleData AS(SELECT *
FROM (
SELECT 11515225, 'LEH100511', 'EURUSEC', '1 20D86,', 'LOUDSPEAKERS,' UNION ALL
SELECT 11515225, 'LEH100511', 'EURUSEC', '1 40D86,', 'MICROPHONES'
) d (BOOKING_ID, BOOKING_NUM, TRADE_CD, BOOKING_EQUIP, GEN_COMMOD_CD)
)
SELECT DISTINCT s.BOOKING_ID, s.BOOKING_NUM, s.TRADE_CD, a.BOOKING_EQUIP, b.GEN_COMMOD_CD
FROM SampleData s
CROSS APPLY (
SELECT
=
STUFF(
(SELECT ', ' + BOOKING_EQUIP
FROM SampleData
WHERE BOOKING_ID = s.BOOKING_ID AND BOOKING_NUM = s.BOOKING_NUM AND TRADE_CD = s.TRADE_CD
ORDER BY BOOKING_EQUIP
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,2,'')
) a (BOOKING_EQUIP)
CROSS APPLY (
SELECT
=
STUFF(
(SELECT ', ' + GEN_COMMOD_CD
FROM SampleData
WHERE BOOKING_ID = s.BOOKING_ID AND BOOKING_NUM = s.BOOKING_NUM AND TRADE_CD = s.TRADE_CD
ORDER BY GEN_COMMOD_CD
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,2,'')
) b (GEN_COMMOD_CD)
Yes, exactly like this !!! but the only problem is I have some other tables that are also being used in the query as well. Can that be taken into account as well ?
Thanks.
June 20, 2012 at 2:06 am
SGT_squeequal (6/19/2012)
the reason you get duplicates is because of your joins are returning courtesan product, can you just explain how you want your results 🙂
The output should be like this :
BOOKING_ID BOOKING_NUM TRADE_CD BOOKING_EQUIP GEN_COMMOD_CD
11515225 LEH100511 EURUSEC 1 20D86, 2 40D86, 3 60D86, 4 80D86 LOUDSPEAKERS, MICROPHONES,
June 20, 2012 at 2:11 am
pwalter83 (6/20/2012)
... I have some other tables that are also being used in the query as well. Can that be taken into account as well ?Thanks.
Of course. Can you post a query which generates the resultset without aggregating some of the columns?
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 2:27 am
ChrisM@Work (6/20/2012)
pwalter83 (6/20/2012)
... I have some other tables that are also being used in the query as well. Can that be taken into account as well ?Thanks.
Of course. Can you post a query which generates the resultset without aggregating some of the columns?
Thanks a lot, Chris, can this query be amended to take your solution into account:
------------------------------------------------------------------
SELECT MB.BOOKING_ID,BOOKING_NUM, cast(EQ_CNT as varchar) + ' ' + KL_EQUIPMENT_TYPE_CD + ',' AS [BOOKING EQUIPMENT], GENERAL_COMMODITY_CD + ',' AS GENERAL_COMMODITY_CD
FROM MG_BOOKING MB
LEFT JOIN
(
SELECT
BOOKING_ID,
MAX(CASE WHEN COMPANY_ROLE_CD = 'SH' THEN PARTY_NAME ELSE NULL END) AS [SHIPPER NAME],
MAX(CASE WHEN COMPANY_ROLE_CD = 'BK' THEN PARTY_NAME ELSE NULL END) AS [BOOKING PARTY],
MAX(CASE WHEN COMPANY_ROLE_CD = 'FW' THEN PARTY_NAME ELSE NULL END) AS [FORWARDER NAME]
FROM MG_BOOKING_PARTY
WHERE COMPANY_ROLE_CD IN ('SH','BK','FW')
GROUP BY BOOKING_ID
)MGP
ON MB.BOOKING_ID = MGP.BOOKING_ID
INNER JOIN MG_BOOKING_COMMODITY MBC
ON MB.BOOKING_ID = MBC.BOOKING_ID
INNER JOIN MG_ISEC_APPLICATION_USERS MIAU
ON MB.CREATE_USER_ID = MIAU.[USER_ID]
-- 18/06/12 - req to add MG_BOOKING_EQUIPMENT_REQ table
INNER JOIN MG_BOOKING_EQUIPMENT_REQ MBER
ON MB.BOOKING_ID = MBER.BOOKING_ID
INNER JOIN MG_OFFICE MO
ON MB.BOOKING_OFFICE_CD = MO.OFFICE_CD
AND MO.ORGANIZATION_COMPANY_CD = 'KFR'
AND MB.BOOKING_TYPE_CD = 'FCL'
AND MB.BOOKING_STATUS_CD = 'F'
AND MB.CREATE_DT > = GETDATE() - 75
AND (MB.SC_NUM NOT IN (SELECT SC_NUM from MiniFCA..MF_SC_NUMBER)
OR MB.SC_NUM IS NULL)
and MB.BOOKING_ID = '11515225'
--GROUP BY MB.BOOKING_ID
ORDER BY MB.BOOKING_ID
------------------------------------------------------------------
June 20, 2012 at 4:42 am
Thanks Paul: try this...
SELECT
MB.BOOKING_ID,
BOOKING_NUM,
--------------------------------------------------------
a.[BOOKING EQUIPMENT],
--------------------------------------------------------
GENERAL_COMMODITY_CD + ',' AS GENERAL_COMMODITY_CD -- qualify this column with a table alias so others know which table it's from
FROM MG_BOOKING MB
LEFT JOIN (
SELECT
BOOKING_ID,
MAX(CASE WHEN COMPANY_ROLE_CD = 'SH' THEN PARTY_NAME ELSE NULL END) AS [SHIPPER NAME],
MAX(CASE WHEN COMPANY_ROLE_CD = 'BK' THEN PARTY_NAME ELSE NULL END) AS [BOOKING PARTY],
MAX(CASE WHEN COMPANY_ROLE_CD = 'FW' THEN PARTY_NAME ELSE NULL END) AS [FORWARDER NAME]
FROM MG_BOOKING_PARTY
WHERE COMPANY_ROLE_CD IN ('SH','BK','FW')
GROUP BY BOOKING_ID
) MGP
ON MB.BOOKING_ID = MGP.BOOKING_ID
INNER JOIN MG_BOOKING_COMMODITY MBC
ON MB.BOOKING_ID = MBC.BOOKING_ID
INNER JOIN MG_ISEC_APPLICATION_USERS MIAU
ON MB.CREATE_USER_ID = MIAU.[USER_ID]
-- 18/06/12 - req to add MG_BOOKING_EQUIPMENT_REQ table
--------------------------------------------------------
CROSS APPLY (
SELECT
=
STUFF(
(SELECT '; ' + CAST(mber.EQ_CNT AS VARCHAR) + ' ' + mber.KL_EQUIPMENT_TYPE_CD
FROM MG_BOOKING_EQUIPMENT_REQ mber
WHERE mber.BOOKING_ID = mb.BOOKING_ID
AND mber.BOOKING_NUM = mb.BOOKING_NUM AND mber.TRADE_CD = mb.TRADE_CD
ORDER BY KL_EQUIPMENT_TYPE_CD
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,2,'')
) a (BOOKING_EQUIPMENT)
--------------------------------------------------------
INNER JOIN MG_OFFICE MO
ON MB.BOOKING_OFFICE_CD = MO.OFFICE_CD
AND MO.ORGANIZATION_COMPANY_CD = 'KFR'
AND MB.BOOKING_TYPE_CD = 'FCL'
AND MB.BOOKING_STATUS_CD = 'F'
AND MB.CREATE_DT > = GETDATE() - 75
AND (MB.SC_NUM NOT IN (SELECT SC_NUM from MiniFCA..MF_SC_NUMBER)
OR MB.SC_NUM IS NULL)
and MB.BOOKING_ID = '11515225'
--GROUP BY MB.BOOKING_ID
ORDER BY MB.BOOKING_ID
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 5:08 am
ChrisM@Work (6/20/2012)
Thanks Paul: try this...
SELECTMB.BOOKING_ID,
BOOKING_NUM,
--------------------------------------------------------
a.[BOOKING EQUIPMENT],
--------------------------------------------------------
GENERAL_COMMODITY_CD + ',' AS GENERAL_COMMODITY_CD -- qualify this column with a table alias so others know which table it's from
FROM MG_BOOKING MB
LEFT JOIN (
SELECT
BOOKING_ID,
MAX(CASE WHEN COMPANY_ROLE_CD = 'SH' THEN PARTY_NAME ELSE NULL END) AS [SHIPPER NAME],
MAX(CASE WHEN COMPANY_ROLE_CD = 'BK' THEN PARTY_NAME ELSE NULL END) AS [BOOKING PARTY],
MAX(CASE WHEN COMPANY_ROLE_CD = 'FW' THEN PARTY_NAME ELSE NULL END) AS [FORWARDER NAME]
FROM MG_BOOKING_PARTY
WHERE COMPANY_ROLE_CD IN ('SH','BK','FW')
GROUP BY BOOKING_ID
) MGP
ON MB.BOOKING_ID = MGP.BOOKING_ID
INNER JOIN MG_BOOKING_COMMODITY MBC
ON MB.BOOKING_ID = MBC.BOOKING_ID
INNER JOIN MG_ISEC_APPLICATION_USERS MIAU
ON MB.CREATE_USER_ID = MIAU.[USER_ID]
-- 18/06/12 - req to add MG_BOOKING_EQUIPMENT_REQ table
--------------------------------------------------------
CROSS APPLY (
SELECT
=
STUFF(
(SELECT '; ' + CAST(mber.EQ_CNT AS VARCHAR) + ' ' + mber.KL_EQUIPMENT_TYPE_CD
FROM MG_BOOKING_EQUIPMENT_REQ mber
WHERE mber.BOOKING_ID = mb.BOOKING_ID
AND mber.BOOKING_NUM = mb.BOOKING_NUM AND mber.TRADE_CD = mb.TRADE_CD
ORDER BY KL_EQUIPMENT_TYPE_CD
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,2,'')
) a (BOOKING_EQUIPMENT)
--------------------------------------------------------
INNER JOIN MG_OFFICE MO
ON MB.BOOKING_OFFICE_CD = MO.OFFICE_CD
AND MO.ORGANIZATION_COMPANY_CD = 'KFR'
AND MB.BOOKING_TYPE_CD = 'FCL'
AND MB.BOOKING_STATUS_CD = 'F'
AND MB.CREATE_DT > = GETDATE() - 75
AND (MB.SC_NUM NOT IN (SELECT SC_NUM from MiniFCA..MF_SC_NUMBER)
OR MB.SC_NUM IS NULL)
and MB.BOOKING_ID = '11515225'
--GROUP BY MB.BOOKING_ID
ORDER BY MB.BOOKING_ID
Thanks a lot Chris, that works just fine.
actually I was trying your solution to get the same result for another column- GENERAL_COMMODITY_CD (from the MG_BOOKING_COMMODITY table) and I did the following but somehow it doesnt work. Could you please tell what I may be doing wrong:
---------------------------------
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 5:18 am
pwalter83 (6/20/2012)
... I did the following but somehow it doesnt work. Could you please tell what I may be doing wrong:---------------------------------
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)
---------------------------------
It doesn't work in what way? An error message?
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 5:24 am
ChrisM@Work (6/20/2012)
pwalter83 (6/20/2012)
... I did the following but somehow it doesnt work. Could you please tell what I may be doing wrong:---------------------------------
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)
---------------------------------
It doesn't work in what way? An error message?
No there is no error message, it just doesnt concatenate the values for GEN_COMMOD_CD like its doing for Booking_Equipment.
For e.g. it should show the value for GEN_COMMOD_CD as concatenated below (the same way as its doing for Booking_Equip:
BOOKING_ID BOOKING_NUM TRADE_CD BOOKING_EQUIP GEN_COMMOD_CD
11515225 LEH100511 EURUSEC 1 20D86, 2 40D86, 3 60D86, 4 80D86 LOUDSPEAKERS, MICROPHONES,
June 20, 2012 at 5:33 am
Are you seeing one row per BOOKING_ID in your output?
- here's where adequate sample data would have helped 🙂
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 5:48 am
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
June 20, 2012 at 5:51 am
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.
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
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply