Two rows of same table into as one row.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Two rows of same table into as one row.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-04-06 : 20:03:39

Senthil writes "Select
PMI.PatientCode,
PMI.InsuranceCode AS PrimInsCode,
'' AS SecInsCode
From
Patient_Mst_Insurance PMI
WHERE
InsuranceFlag = 'Primary'

And
PatientCode = '309506'

Union ALL

Select
PMI.PatientCode,
'' AS PrimInsCode,
PMI.InsuranceCode AS SecInsCode

From
Patient_Mst_Insurance PMI
Where
InsuranceFlag = 'Secondary'

And
PatientCode = '309506'

The above returns as

PatientCode PrimInsCode SecInsCode
309506 MEDICARE
309506 AARP

Is any way to return as follows

PatientCode PrimInsCode SecInsCode
309506 MEDICARE AARP"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-06 : 21:28:10

[code]SELECT PatientCode, MAX(PrimInsCode), MAX(SecInsCode)
FROM
(
Select PMI.PatientCode, PMI.InsuranceCode AS PrimInsCode, '' AS SecInsCode
From Patient_Mst_Insurance PMI
WHERE InsuranceFlag = 'Primary'
And PatientCode = '309506'

Union ALL

Select PMI.PatientCode, '' AS PrimInsCode, PMI.InsuranceCode AS SecInsCode
From Patient_Mst_Insurance PMI
Where InsuranceFlag = 'Secondary'
And PatientCode = '309506'
) a
GROUP BY PatientCode[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources