Merging rows

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Merging rows

14 posts, Page 1 of 2

1 2 »»

Merging rows

Rate Topic

Display Mode

Topic Options

Author

Message

prady_1988

Posted Friday, October 05, 2012 6:00 AM

Grasshopper

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

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

Post #1368963

Mark-101232

Posted Friday, October 05, 2012 6:11 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:53 AM
Points: 1,501, Visits: 18,208

Try this

SELECT AssetID, AssetName, MAX(Relationship) AS Relationship, MAX(Company) AS Company
FROM myTable
GROUP BY AssetID, AssetName

____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.

Post #1368967

prady_1988

Posted Friday, October 05, 2012 6:19 AM

Grasshopper

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

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

Post #1368975

Mark-101232

Posted Friday, October 05, 2012 6:26 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:53 AM
Points: 1,501, Visits: 18,208

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

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

Post #1368980

prady_1988

Posted Friday, October 05, 2012 6:41 AM

Grasshopper

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

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

Post #1368990

Mark-101232

Posted Friday, October 05, 2012 6:44 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:53 AM
Points: 1,501, Visits: 18,208

prady_1988 (10/5/2012)

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

Post #1368993

ChrisM@Work

Posted Friday, October 05, 2012 6:44 AM

SSCertifiable

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

prady_1988 (10/5/2012)

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

Post #1368994

prady_1988

Posted Friday, October 05, 2012 6:52 AM

Grasshopper

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

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

Post #1368998

ChrisM@Work

Posted Friday, October 05, 2012 7:24 AM

SSCertifiable

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

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}

Post #1369023

Sean Lange

Posted Friday, October 05, 2012 7:33 AM

SSCrazy Eights

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

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

_______________________________________________________________

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 #1369032

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions