Merging rows

Rate Topic

Display Mode

Topic Options

Author

Message

ChrisM@Work

Posted Friday, October 05, 2012 7:36 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

Sean Lange (10/5/2012)

Still nothing to test with but I think this might be close to what you want...

select A, b, MAX(C), MAX(D), MAX(E), MAX(F), g
from
(
	SELECT distinct
	BASE1.AssetID AS a
	,BASE1.Name As b
	,case when BASE2.AssetID like 'AST%' then 'Asset Records' else '' end as c
	,replace(RELA.Name,char(10),'') As d
	,BASE2.AssetID AS e
	,BASE2.Name AS f
	,BASE1.Company AS g
	FROM
	BMC_CORE_BMC_BaseElement BASE1 (NOLOCK)
	LEFT JOIN BMC_CORE_BMC_BaseRelationship RELA  (NOLOCK)
	ON BASE1.InstanceId = RELA.Source_InstanceId
	LEFT JOIN BMC_CORE_BMC_BaseElement BASE2 (NOLOCK)
	ON RELA.Destination_InstanceId = BASE2.InstanceId  AND Base2.DatasetId='BMC.ASSET'
	LEFT JOIN AST_CMDB_Associations AST  (NOLOCK)
	ON AST.request_id02 = BASE1.ReconciliationIdentity
	and AST.Request_ID02 != '0' and AST.Request_ID01 != '0' 
	and AST.form_name01 <> 'TMS:TASK' and AST.form_name01 <> 'WOI:WorkOrder'

	where 1=1
	and BASE1.AssetID = 'abcd'

	UNION ALL

	SELECT distinct
	BASE1.AssetID AS a 
	,BASE1.Name As b
	,case
	when AST.form_name01 = 'HPD:Help Desk' then 'Incident'
	when AST.form_name01 = 'CHG:Infrastructure Change' then 'Infrastructure Change'
	when AST.form_name01 = 'PBM:Known Error' then 'Known Error'
	when AST.form_name01 = 'PBM:Problem Investigation' then 'Problem Investigation'
	when AST.form_name01 = 'RMS:Release' then 'Release'
	when AST.form_name01 = 'PBM:Solution Database' then 'Solution Database' end As c
	,replace(CMDB_Associations.Alias_Value COLLATE DATABASE_DEFAULT,char(10),'') AS d 
	,AST.Request_id01 AS e 
	,AST.Request_Description01 AS f
	,BASE1.Company AS g

	FROM
	BMC_CORE_BMC_BaseElement BASE1 (NOLOCK)
	LEFT JOIN BMC_CORE_BMC_BaseRelationship RELA  (NOLOCK)
	ON BASE1.InstanceId = RELA.Source_InstanceId
	LEFT JOIN BMC_CORE_BMC_BaseElement BASE2 (NOLOCK)
	ON RELA.Destination_InstanceId = BASE2.InstanceId  AND Base2.DatasetId='BMC.ASSET'
	LEFT JOIN AST_CMDB_Associations AST  (NOLOCK)
	ON AST.request_id02 = BASE1.ReconciliationIdentity
	and AST.Request_ID02 != '0' and AST.Request_ID01 != '0' 
	and AST.form_name01 <> 'TMS:TASK' and AST.form_name01 <> 'WOI:WorkOrder'

	where 1=1
	and BASE1.AssetID = 'abcd'
) x
group by A, b, g

These queries are identical after the FROM. Any chance you can consolidate this so you only need 1 query?

As a side note, why all the nolock hints?

--EDIT--
Didn't refresh before posting. It seems Chris has much the same comment as me. Tongue

A second pair of eyes and the same conclusion - that's good enough for me, Sean.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1369036

prady_1988

Posted Friday, October 05, 2012 8:45 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:45 AM
Points: 20, Visits: 127

Sean Lange (10/5/2012)

Still nothing to test with but I think this might be close to what you want...

select A, b, MAX(C), MAX(D), MAX(E), MAX(F), g
from
(
	SELECT distinct
	BASE1.AssetID AS a
	,BASE1.Name As b
	,case when BASE2.AssetID like 'AST%' then 'Asset Records' else '' end as c
	,replace(RELA.Name,char(10),'') As d
	,BASE2.AssetID AS e
	,BASE2.Name AS f
	,BASE1.Company AS g
	FROM
	BMC_CORE_BMC_BaseElement BASE1 (NOLOCK)
	LEFT JOIN BMC_CORE_BMC_BaseRelationship RELA  (NOLOCK)
	ON BASE1.InstanceId = RELA.Source_InstanceId
	LEFT JOIN BMC_CORE_BMC_BaseElement BASE2 (NOLOCK)
	ON RELA.Destination_InstanceId = BASE2.InstanceId  AND Base2.DatasetId='BMC.ASSET'
	LEFT JOIN AST_CMDB_Associations AST  (NOLOCK)
	ON AST.request_id02 = BASE1.ReconciliationIdentity
	and AST.Request_ID02 != '0' and AST.Request_ID01 != '0' 
	and AST.form_name01 <> 'TMS:TASK' and AST.form_name01 <> 'WOI:WorkOrder'

	where 1=1
	and BASE1.AssetID = 'abcd'

	UNION ALL

	SELECT distinct
	BASE1.AssetID AS a 
	,BASE1.Name As b
	,case
	when AST.form_name01 = 'HPD:Help Desk' then 'Incident'
	when AST.form_name01 = 'CHG:Infrastructure Change' then 'Infrastructure Change'
	when AST.form_name01 = 'PBM:Known Error' then 'Known Error'
	when AST.form_name01 = 'PBM:Problem Investigation' then 'Problem Investigation'
	when AST.form_name01 = 'RMS:Release' then 'Release'
	when AST.form_name01 = 'PBM:Solution Database' then 'Solution Database' end As c
	,replace(CMDB_Associations.Alias_Value COLLATE DATABASE_DEFAULT,char(10),'') AS d 
	,AST.Request_id01 AS e 
	,AST.Request_Description01 AS f
	,BASE1.Company AS g

	FROM
	BMC_CORE_BMC_BaseElement BASE1 (NOLOCK)
	LEFT JOIN BMC_CORE_BMC_BaseRelationship RELA  (NOLOCK)
	ON BASE1.InstanceId = RELA.Source_InstanceId
	LEFT JOIN BMC_CORE_BMC_BaseElement BASE2 (NOLOCK)
	ON RELA.Destination_InstanceId = BASE2.InstanceId  AND Base2.DatasetId='BMC.ASSET'
	LEFT JOIN AST_CMDB_Associations AST  (NOLOCK)
	ON AST.request_id02 = BASE1.ReconciliationIdentity
	and AST.Request_ID02 != '0' and AST.Request_ID01 != '0' 
	and AST.form_name01 <> 'TMS:TASK' and AST.form_name01 <> 'WOI:WorkOrder'

	where 1=1
	and BASE1.AssetID = 'abcd'
) x
group by A, b, g

Thanks guys, seems to work fine for one record:

AST000000089076	cdpq-dicrc-arccon-02.SCDPQ	Asset Records	HOSTEDSYSTEMCOMPONENTS	AST000000519575	CDPQ-DICRC-ARCCON-02-ARCS
AST000000089076	cdpq-dicrc-arccon-02.SCDPQ

AST000000089076	cdpq-dicrc-arccon-02.SCDPQ	Asset Records	HOSTEDSYSTEMCOMPONENTS	AST000000519575	CDPQ-DICRC-ARCCON-02-ARCS

But when I have multiple records:

AST000000046006	CMOS10D -	Incident	Related to	INC000000042707	INC000000042707: test for GVI
AST000000046006	CMOS10D -	Incident	Related to	INC000000042708	GVI Testing
AST000000046006	CMOS10D -	Incident	Related to	INC000000042709	testggg
AST000000046006	CMOS10D -	Incident	Related to	INC000000048861	INC000000048861: <*** PLEASE DISREGARD *** this is a test  Incident Modified
AST000000046006	CMOS10D -	Incident	Related to	INC000000048964	AAAAAAAAAAAAA
AST000000046006	CMOS10D -	Known Error	Related to	PKE000000005002	PKE000000005002: ITSM Issue

I get only the last record:

AST000000046006	CMOS10D -	Known Error	Related to	PKE000000005002	PKE000000005002: ITSM Issue

Post #1369107

Sean Lange

Posted Friday, October 05, 2012 8:56 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 8,641, Visits: 8,273

prady_1988 (10/5/2012)

Sean Lange (10/5/2012)

Still nothing to test with but I think this might be close to what you want...

select A, b, MAX(C), MAX(D), MAX(E), MAX(F), g
from
(
	SELECT distinct
	BASE1.AssetID AS a
	,BASE1.Name As b
	,case when BASE2.AssetID like 'AST%' then 'Asset Records' else '' end as c
	,replace(RELA.Name,char(10),'') As d
	,BASE2.AssetID AS e
	,BASE2.Name AS f
	,BASE1.Company AS g
	FROM
	BMC_CORE_BMC_BaseElement BASE1 (NOLOCK)
	LEFT JOIN BMC_CORE_BMC_BaseRelationship RELA  (NOLOCK)
	ON BASE1.InstanceId = RELA.Source_InstanceId
	LEFT JOIN BMC_CORE_BMC_BaseElement BASE2 (NOLOCK)
	ON RELA.Destination_InstanceId = BASE2.InstanceId  AND Base2.DatasetId='BMC.ASSET'
	LEFT JOIN AST_CMDB_Associations AST  (NOLOCK)
	ON AST.request_id02 = BASE1.ReconciliationIdentity
	and AST.Request_ID02 != '0' and AST.Request_ID01 != '0' 
	and AST.form_name01 <> 'TMS:TASK' and AST.form_name01 <> 'WOI:WorkOrder'

	where 1=1
	and BASE1.AssetID = 'abcd'

	UNION ALL

	SELECT distinct
	BASE1.AssetID AS a 
	,BASE1.Name As b
	,case
	when AST.form_name01 = 'HPD:Help Desk' then 'Incident'
	when AST.form_name01 = 'CHG:Infrastructure Change' then 'Infrastructure Change'
	when AST.form_name01 = 'PBM:Known Error' then 'Known Error'
	when AST.form_name01 = 'PBM:Problem Investigation' then 'Problem Investigation'
	when AST.form_name01 = 'RMS:Release' then 'Release'
	when AST.form_name01 = 'PBM:Solution Database' then 'Solution Database' end As c
	,replace(CMDB_Associations.Alias_Value COLLATE DATABASE_DEFAULT,char(10),'') AS d 
	,AST.Request_id01 AS e 
	,AST.Request_Description01 AS f
	,BASE1.Company AS g

	FROM
	BMC_CORE_BMC_BaseElement BASE1 (NOLOCK)
	LEFT JOIN BMC_CORE_BMC_BaseRelationship RELA  (NOLOCK)
	ON BASE1.InstanceId = RELA.Source_InstanceId
	LEFT JOIN BMC_CORE_BMC_BaseElement BASE2 (NOLOCK)
	ON RELA.Destination_InstanceId = BASE2.InstanceId  AND Base2.DatasetId='BMC.ASSET'
	LEFT JOIN AST_CMDB_Associations AST  (NOLOCK)
	ON AST.request_id02 = BASE1.ReconciliationIdentity
	and AST.Request_ID02 != '0' and AST.Request_ID01 != '0' 
	and AST.form_name01 <> 'TMS:TASK' and AST.form_name01 <> 'WOI:WorkOrder'

	where 1=1
	and BASE1.AssetID = 'abcd'
) x
group by A, b, g

Thanks guys, seems to work fine for one record:

AST000000089076	cdpq-dicrc-arccon-02.SCDPQ	Asset Records	HOSTEDSYSTEMCOMPONENTS	AST000000519575	CDPQ-DICRC-ARCCON-02-ARCS
AST000000089076	cdpq-dicrc-arccon-02.SCDPQ

AST000000089076	cdpq-dicrc-arccon-02.SCDPQ	Asset Records	HOSTEDSYSTEMCOMPONENTS	AST000000519575	CDPQ-DICRC-ARCCON-02-ARCS

But when I have multiple records:

AST000000046006	CMOS10D -	Incident	Related to	INC000000042707	INC000000042707: test for GVI
AST000000046006	CMOS10D -	Incident	Related to	INC000000042708	GVI Testing
AST000000046006	CMOS10D -	Incident	Related to	INC000000042709	testggg
AST000000046006	CMOS10D -	Incident	Related to	INC000000048861	INC000000048861: <*** PLEASE DISREGARD *** this is a test  Incident Modified
AST000000046006	CMOS10D -	Incident	Related to	INC000000048964	AAAAAAAAAAAAA
AST000000046006	CMOS10D -	Known Error	Related to	PKE000000005002	PKE000000005002: ITSM Issue

I get only the last record:

AST000000046006	CMOS10D -	Known Error	Related to	PKE000000005002	PKE000000005002: ITSM Issue

Are you familiar with aggregate data? Notice the query has MAX and group by. That means it is only going to get 1 row. If you need assistance with this you need to post ddl and sample data. We are totally shooting blind. Take a look at the first link in my signature for best practices when posting questions.

I have to ask again....why the NOLOCK hints?

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1369116

prady_1988

Posted Monday, October 08, 2012 6:09 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:45 AM
Points: 20, Visits: 127

I apologize for posting the wrong way, I was in a hurry and did not go through the best practices. Anyway, the issue is solved now, used a couple of sub queries, case statements and business logic, not even using the UNION now.

And I removed the NOLOCK hints (thanks for pointing it out Smile

), I was just modifying an existing stored procedure.

Thanks a lot for your time and help,
Prady

Post #1369752

« Prev Topic | Next Topic »