How do I return a row that contains repeated fields

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)

How do I return a row that contains repeated fields

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-18 : 08:49:31

Paul writes "Hi Guys

My problem in a nutshell is this. I have a table, which has a primary key (ID), name (Name) and two value fields (FieldA, FieldB). The table looks like this


ID     Name           FieldA     FieldB
---------------------------------------
1      Paul           1          5
2      Paul           1          2
3      Paul           1          5
4      Paul           1          5
5      Paul           1          5
6      Paul           1          5
7      Bill           1          2
8      Bill           1          5
9      Mary           1          2

I want to produce a report in the following format


Name    FieldA FieldB FieldA FieldB FieldA FieldB FieldA FieldB 
===============================================================
Paul    1      5      1      2      1      5      1      5
Paul    1      5
Bill    1      2      1      5
Mary    1      2

It should be fairly simple, but conceptually I am really struggling. Can you give me any help?

Many thanks

Paul Hollyer"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-18 : 09:03:18

select t.name ,
t1FieldA = t1.FieldA ,
t1FieldB = t1.FieldB ,
t2FieldA = t2.FieldA ,
t2FieldB = t2.FieldB ,
...
from(select distinct Name from tbl) t
left join (select * from tbl where ID = 1) t1 on t1.name = t.name
left join (select * from tbl where ID = 2) t2 on t2.name = t.name
...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources