Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Merging rows


Merging rows

Author
Message
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8960 Visits: 19019
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 = 'PBMTongueroblem 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. :-P


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
prady_1988
prady_1988
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
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 = 'PBMTongueroblem 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. :-P


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


To

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

Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16550 Visits: 17004
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 = 'PBMTongueroblem 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. :-P


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


To

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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
prady_1988
prady_1988
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
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 :-) ), I was just modifying an existing stored procedure.


Thanks a lot for your time and help,
Prady
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search