Merging rows

• prady_1988

  Say Hey Kid

  Points: 680

  More actions

  October 5, 2012 at 6:00 am

  #282626

  Hi All,

  Is it possible to combine two rows into a single row?

  example:

  Asset ID Asset Name Relationship Company

  1234 abcd Incident

  1234 abcd zxcv

  I am getting this through a Union of 2 select queries.

  Need both of them in a single row:

  1234 abcd Incident zxcv

  Basically, its something like

  select 'a' as column1, ' ' as column2

  union

  select ' ' as column1, 'b' as column2

  final result should be something like

  column1 column2

  a b

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  October 5, 2012 at 6:11 am

  #1546015

  Try this

  SELECT AssetID, AssetName, MAX(Relationship) AS Relationship, MAX(Company) AS Company

  FROM myTable

  GROUP BY AssetID, AssetName

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• prady_1988

  Say Hey Kid

  Points: 680

  More actions

  October 5, 2012 at 6:19 am

  #1546021

  Mark-101232 (10/5/2012)

  ---

  Try this

  SELECT AssetID, AssetName, MAX(Relationship) AS Relationship, MAX(Company) AS Company

  FROM myTable

  GROUP BY AssetID, AssetName

  Thanks Mark, but the problem is "Relationship" and "Company" columns are from different tables. Hence using two Select queries with a Union

  Where as AssetID and AssetName are from a single table. Therefore there are 3 tables involved

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  October 5, 2012 at 6:26 am

  #1546025

  prady_1988 (10/5/2012)

  ---

  Mark-101232 (10/5/2012)

  ---

  Try this

  SELECT AssetID, AssetName, MAX(Relationship) AS Relationship, MAX(Company) AS Company

  FROM myTable

  GROUP BY AssetID, AssetName

  Thanks Mark, but the problem is "Relationship" and "Company" columns are from different tables. Hence using two Select queries with a Union

  Where as AssetID and AssetName are from a single table. Therefore there are 3 tables involved

  Perhaps you could post the DDL for the tables. Meanwhile, this may work

  SELECT t.AssetID, t.AssetName, r.Relationship, c.Company

  FROM myTable t

  LEFT OUTER JOIN RelationshipTable r ON r.AssetID = t.AssetID

  LEFT OUTER JOIN Company c ON c.AssetID = t.AssetID

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• prady_1988

  Say Hey Kid

  Points: 680

  More actions

  October 5, 2012 at 6:41 am

  #1546033

  Sorry, I may have mislead you.

  The values under Relationship is taken from 2 tables.

  Table1 -> AssetID, AssetName

  Table2 -> Relationship1

  Table3 -> Relationship2

  Table4 -> Company

  Hence for each record I am getting duplicate values since one of the Relationship is null and the other has value

  The sample query I have something like:

  Select A.AssetID, A.AssetName, B.Relate as Relationship, C.Company

  from table and joins

  union

  Select A.AssetID, A.AssetName, B.Relatedby as Relationship, C.Company

  from table and joins

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  October 5, 2012 at 6:44 am

  #1546035

  prady_1988 (10/5/2012)

  ---

  Sorry, I may have mislead you.

  The values under Relationship is taken from 2 tables.

  Table1 -> AssetID, AssetName

  Table2 -> Relationship1

  Table3 -> Relationship2

  Table4 -> Company

  Hence for each record I am getting duplicate values since one of the Relationship is null and the other has value

  The sample query I have something like:

  Select A.AssetID, A.AssetName, B.Relate as Relationship, C.Company

  from table and joins

  union

  Select A.AssetID, A.AssetName, B.Relatedby as Relationship, C.Company

  from table and joins

  Bit of a guess here. Posting DDL with some sample data would help a lot.

  WITH CTE AS (

  Select A.AssetID, A.AssetName, B.Relate as Relationship, C.Company

  from table and joins

  union

  Select A.AssetID, A.AssetName, B.Relatedby as Relationship, C.Company

  from table and joins

  )

  SELECT AssetID, AssetName, MAX(Relationship) AS Relationship, MAX(Company) AS Company

  FROM CTE

  GROUP BY AssetID, AssetName

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 5, 2012 at 6:44 am

  #1546036

  prady_1988 (10/5/2012)

  ---

  Sorry, I may have mislead you.

  The values under Relationship is taken from 2 tables.

  Table1 -> AssetID, AssetName

  Table2 -> Relationship1

  Table3 -> Relationship2

  Table4 -> Company

  Hence for each record I am getting duplicate values since one of the Relationship is null and the other has value

  The sample query I have something like:

  Select A.AssetID, A.AssetName, B.Relate as Relationship, C.Company

  from table and joins

  union

  Select A.AssetID, A.AssetName, B.Relatedby as Relationship, C.Company

  from table and joins

  If you post the whole query, we won't have to make so many guesses.

  ^{“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

• prady_1988

  Say Hey Kid

  Points: 680

  More actions

  October 5, 2012 at 6:52 am

  #1546040

  Its a pretty big query but here it is:

  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'

  This would give something like:

  a b c d e f g

  abcd SDPQ OPDA

  abcd SDPQ Asset Record test dcba fgh OPDA

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 5, 2012 at 7:24 am

  #1546063

  prady_1988 (10/5/2012)

  ---

  Its a pretty big query but here it is:

  <<snip>>

  The FROM list and WHERE clause of both of the queries is identical:

  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 BASE1.AssetID = 'abcd'

  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 BASE1.AssetID = 'abcd'

  - and deduplication in each is performed by the DISTINCT, which operates on the output list. If you can fathom out how to more predictably deduplicate the result sets using GROUP BY, then you would get away with one query.

  ^{“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

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  October 5, 2012 at 7:33 am

  #1546071

  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. 😛

  _______________________________________________________________

  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/

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 5, 2012 at 7:36 am

  #1546075

  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. 😛

  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

• prady_1988

  Say Hey Kid

  Points: 680

  More actions

  October 5, 2012 at 8:45 am

  #1546136

  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. 😛

  Thanks guys, seems to work fine for one record:

  AST000000089076cdpq-dicrc-arccon-02.SCDPQAsset RecordsHOSTEDSYSTEMCOMPONENTSAST000000519575CDPQ-DICRC-ARCCON-02-ARCS

  AST000000089076cdpq-dicrc-arccon-02.SCDPQ

  To

  AST000000089076cdpq-dicrc-arccon-02.SCDPQAsset RecordsHOSTEDSYSTEMCOMPONENTSAST000000519575CDPQ-DICRC-ARCCON-02-ARCS

  But when I have multiple records:

  AST000000046006CMOS10D -IncidentRelated toINC000000042707INC000000042707: test for GVI

  AST000000046006CMOS10D -IncidentRelated toINC000000042708GVI Testing

  AST000000046006CMOS10D -IncidentRelated toINC000000042709testggg

  AST000000046006CMOS10D -IncidentRelated toINC000000048861INC000000048861: <*** PLEASE DISREGARD *** this is a test Incident Modified

  AST000000046006CMOS10D -IncidentRelated toINC000000048964AAAAAAAAAAAAA

  AST000000046006CMOS10D -Known ErrorRelated toPKE000000005002PKE000000005002: ITSM Issue

  I get only the last record:

  AST000000046006CMOS10D -Known ErrorRelated toPKE000000005002PKE000000005002: ITSM Issue

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  October 5, 2012 at 8:56 am

  #1546145

  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

  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. 😛

  Thanks guys, seems to work fine for one record:

  AST000000089076cdpq-dicrc-arccon-02.SCDPQAsset RecordsHOSTEDSYSTEMCOMPONENTSAST000000519575CDPQ-DICRC-ARCCON-02-ARCS

  AST000000089076cdpq-dicrc-arccon-02.SCDPQ

  To

  AST000000089076cdpq-dicrc-arccon-02.SCDPQAsset RecordsHOSTEDSYSTEMCOMPONENTSAST000000519575CDPQ-DICRC-ARCCON-02-ARCS

  But when I have multiple records:

  AST000000046006CMOS10D -IncidentRelated toINC000000042707INC000000042707: test for GVI

  AST000000046006CMOS10D -IncidentRelated toINC000000042708GVI Testing

  AST000000046006CMOS10D -IncidentRelated toINC000000042709testggg

  AST000000046006CMOS10D -IncidentRelated toINC000000048861INC000000048861: <*** PLEASE DISREGARD *** this is a test Incident Modified

  AST000000046006CMOS10D -IncidentRelated toINC000000048964AAAAAAAAAAAAA

  AST000000046006CMOS10D -Known ErrorRelated toPKE000000005002PKE000000005002: ITSM Issue

  I get only the last record:

  AST000000046006CMOS10D -Known ErrorRelated toPKE000000005002PKE000000005002: 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 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/

• prady_1988

  Say Hey Kid

  Points: 680

  More actions

  October 8, 2012 at 6:09 am

  #1546671

  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