Display multiple records in 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 2005 Forums

Transact-SQL (2005)

Display multiple records in one row

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-20 : 09:05:09

Andy writes "Hi

I am trying to write a query that will return one row of data even though there are multiple records.

For Example. If I want to search for all accounts created in last month.

Table


Name       DOB      AC Create  Account type Account Number
Joe Bloggs 12.06.83 15.02.07   Current      12345678
Joe Bloggs 12.06.83 01.03.07   Saving       87654321
Joe Bloggs 12.06.58 10.03.07   Deposit      13572468
Jane Doe   16.02.58 17.02.07   Current      13578642
Jane Doe   16.02.58 17.02.07   Saving       86421357

I would like to create a simple query to get the results as


Name        DOB      Current No Saving No  Deposit No
Joe Bloggs  12.06.83 12345678   87654321   13572468
Jane Doe    16.02.58 13578642   86421357

Thanks

Andy"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-20 : 09:10:29

[code]
select [Name], [DOB],
[Current No] = max(case when [Account type] = 'Current' then [Account Number] end),
[Saving No] = max(case when [Account type] = 'Saving' then [Account Number] end),
[Deposit No] = max(case when [Account type] = 'Deposit' then [Account Number] end)
from yourtable
group by [Name], [DOB]
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources