February 10, 2011 at 9:55 am
Hello ,
Does anyone know if its posisble to merge 2 or more rows of data using SQL if the data if apart from one column the data is same for both the rows ? The scenario is explained below:
the DDL for a sample table:
CREATE TABLE [dbo].[xyz](
[BOOKING_ID] [numeric](10, 0) NOT NULL,
[BOOKING_TYPE_CD] [varchar](3) NULL,
[BOOKING_STATUS_CD] [char](1) NULL,
[BK_PARTY] [varchar](50) NULL,
[FW_PARTY] [varchar](50) NULL,
[SH_PARTY] [varchar](50) NULL
)
-------------------------------------
the sample data for the above table is:
insert into xyz
values('4746119','FCL','F',NULL,NULL,'KUEHNE + NAGEL (AG & CO.)')
insert into xyz
values('4746119','FCL','F',NULL,'KUEHNE + NAGEL (AG & CO.)',NULL)
insert into xyz
values('4746119','FCL','F','KUEHNE + NAGEL (AG & CO.)',NULL,NULL)
-------------------------------------------------------------------------
is it possible to write a sql query where the above data can be combined into one single line of data
by removing the NULL VALUES and so is displayed as:
4746119 FCL F KUEHNE + NAGEL (AG & CO.) KUEHNE + NAGEL (AG & CO.)
KUEHNE + NAGEL (AG & CO.)
Kind Regards,
Paul
February 10, 2011 at 11:47 am
Something like this?
SELECT
[BOOKING_ID],
[BOOKING_TYPE_CD],
[BOOKING_STATUS_CD] ,
MAX([BK_PARTY]) AS [BK_PARTY],
MAX([FW_PARTY]) AS [FW_PARTY],
MAX([SH_PARTY]) AS [SH_PARTY]
FROM [dbo].[xyz]
GROUP BY [BOOKING_ID],[BOOKING_TYPE_CD],[BOOKING_STATUS_CD]
As a side note: thank you for the ready to use sample data and your expected result. Good job!
February 11, 2011 at 2:52 am
Hello Lutz,
Thanks a ton for your solution. Actually, there is some additional information which I forgot to include. The problem is that the three columns- BK_PARTY, FW_PARTY, SH_PARTY have been created using existing columns from the same table based on a condition which goes as under:
CASE WHEN MGP.COMPANY_ROLE_CD = 'BK' THEN MGP.COMPANY_ROLE_CD END AS [BK_PARTY],
CASE WHEN MGP.COMPANY_ROLE_CD = 'FW' THEN MGP.COMPANY_ROLE_CD END AS [FW_PARTY],
CASE WHEN MGP.COMPANY_ROLE_CD = 'SH' THEN MGP.COMPANY_ROLE_CD END AS [SH_PARTY],
Now would it be possible to merge the rows if the data exists for one column and does not for the other two ? This is where I am stuck...I hope I have explained the scenario fully.
Please also find the query which inlcudes the above mentioned condition.
Kind Regards,
Paul
February 11, 2011 at 11:08 am
I'd move the section in question into a subquery:
SELECT
...
CASE WHEN MGP.COMPANY_ROLE_CD = 'BK' THEN MGP.COMPANY_ROLE_CD END AS [BK_PARTY],
CASE WHEN MGP.COMPANY_ROLE_CD = 'FW' THEN MGP.COMPANY_ROLE_CD END AS [FW_PARTY],
CASE WHEN MGP.COMPANY_ROLE_CD = 'SH' THEN MGP.COMPANY_ROLE_CD END AS [SH_PARTY],
MGP.BK_PARTY,
MGP.FW_PARTY,
MGP.SH_PARTY,
...
INNER JOIN MG_BOOKING_PARTY MGP
ON MGB.BOOKING_ID = MGP.BOOKING_ID
INNER JOIN
(
SELECT
BOOKING_ID,
MAX(CASE WHEN COMPANY_ROLE_CD = 'BK' THEN COMPANY_ROLE_CD ELSE NULL END) AS [BK_PARTY],
MAX(CASE WHEN COMPANY_ROLE_CD = 'FW' THEN COMPANY_ROLE_CD ELSE NULL END) AS [FW_PARTY],
MAX(CASE WHEN COMPANY_ROLE_CD = 'SH' THEN COMPANY_ROLE_CD ELSE NULL END) AS [SH_PARTY],
FROM MG_BOOKING_PARTY
WHERE COMPANY_ROLE_CD IN ('BK','FW','SH')
GROUP BY BOOKING_ID
)MGP
ON MGB.BOOKING_ID = MGP.BOOKING_ID
...
As a side note: other than your original data you now intend to show 'BK','FW', or 'SH'?
(due to WHEN COMPANY_ROLE_CD = ...THEN COMPANY_ROLE_CD)
Or is it a typo? If so, you'd need to adjust the query to match your expected columns.
February 14, 2011 at 2:53 am
Hi Lutz,
Thanks a zillion for saving my day again !!!
Kind Regards,
Paul
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply