How to extr fields frm multiple recs to single rec

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)

How to extr fields frm multiple recs to single rec

Author

Topic

lzpnvsi
Starting Member

1 Post

Posted - 2007-05-15 : 19:51:39

I need to extract fields from multiple records into a single record. The result of my query currently looks like this:-

FieldA FieldB FieldC
11 ABC 123
11 DEF 456
11 GHI 789
12 ABC 123
12 DEF 456
12 GHI 789 etc

But I want:-

FieldA FieldB FieldC FieldD FieldE
11 ABC 123 456 789
12 ABC 123 456 789

How do I do that? Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-15 : 21:59:12

[code]
select FieldA,
FieldB = min(FieldB),
FieldC = max(case when row = 1 then FieldC end),
FieldD = max(case when row = 2 then FieldC end),
FieldE = max(case when row = 3 then FieldC end)
from
(
select *, row = row_number() over (partition by FieldA order by FieldC)
from table
) t
group by FieldA
[/code]

vijayakumar_svk
Yak Posting Veteran

50 Posts

Posted - 2007-05-15 : 22:02:17

Please put the exact result set you want. Why the B columns are data is missing in the result set?

==============================
Work smarter not harder take control of your life be a super achiever

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources