Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Merging rows Expand / Collapse
Author
Message
Posted Friday, October 5, 2012 6:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, October 5, 2012 6:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:59 AM
Points: 1,678, Visits: 19,556
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
Posted Friday, October 5, 2012 6:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, October 5, 2012 6:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:59 AM
Points: 1,678, Visits: 19,556
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




____________________________________________________

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 #1368980
Posted Friday, October 5, 2012 6:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, October 5, 2012 6:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:59 AM
Points: 1,678, Visits: 19,556
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



____________________________________________________

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 #1368993
Posted Friday, October 5, 2012 6:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Post #1368994
Posted Friday, October 5, 2012 6:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, October 5, 2012 7:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Post #1369023
Posted Friday, October 5, 2012 7:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
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 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)
Post #1369032
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse