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


Merging rows


Merging rows

Author
Message
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
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
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 22769
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
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
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
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 22769
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
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
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
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 22769
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
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8921 Visits: 19002
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
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
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 = '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'



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
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8921 Visits: 19002
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
Exploring Recursive CTEs by Example Dwain Camps
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: 16510 Visits: 16985
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

_______________________________________________________________

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)
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