Select output in One Row.... - SQL Server Forums

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

SQL Server Development (2000)

Select output in One Row....

Author

Topic

rbharatha
Starting Member

29 Posts

Posted - 2006-05-23 : 02:22:53

Hi folks,

I have the following query which gives some x records...
I want to display the output of records in the same row if the column value of OfficeID is same for the next row.
I had put an example below for your understanding....
I have 2 rows with the same OfficeID, but the column values of c5-c8 are null in the 1st row and column values of c2-c4 are null in the 2nd row.
I want to merge both the rows if the officeid is same.

SELECT B.PFLOFFICEID AS OFFICEID,
E.tvcharFirstName as f2, E.tvcharLastName as f2, E.PtvcharUserGUID as f3, E.tvcharEMailAddr as f4,
c.tvcharFirstName as f5, c.tvcharLastName as f6, c.PtvcharUserGUID as f7, c.tvcharEMailAddr as f8
FROM TBLOFFICES A
INNER JOIN TBLUSERASSIGNMENTS B ON B.PFLOFFICEID = A.PLOFFICEID
left outer JOIN TBLUSERPROFILES E ON B.PFtvcharUserGUID = E.PtvcharUserGUID AND B.PLROLE = 4
left outer JOIN TBLUSERPROFILES C ON B.PFtvcharUserGUID = C.PtvcharUserGUID AND B.PLROLE = 14
where E.PtvcharUserGUID IS NOT NULL OR C.PtvcharUserGUID IS NOT NULL
order by 1

c1 c2 c3 c4 c5 c6 c7 c8
1 Brian Muy A9 bm NULL NULL NULL NULL
1 NULL NULL NULL NULL Brian Mu A9 bmull

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-23 : 02:42:13

Do you need something like this?
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-23 : 07:17:05

Or maybe this?

--data
declare @t table (c1 int, c2 varchar(10), c3 varchar(10), c4 varchar(10),
    c5 varchar(10), c6 varchar(10), c7 varchar(10), c8 varchar(10), c9 varchar(10))
insert @t
          select 1, 'Brian', 'Muy', 'A9', 'bm', NULL, NULL, NULL, NULL
union all select 1, NULL, NULL, NULL, NULL, 'Brian', 'Mu', 'A9', 'bmull'

--calculation
select c1, max(c2), max(c3), max(c4), max(c5), max(c6), max(c7), max(c8), max(c9)
from @t group by c1

--results
c1                                                                                                  
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 
1           Brian      Muy        A9         bm         Brian      Mu         A9         bmull

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

rbharatha
Starting Member

29 Posts

Posted - 2006-05-23 : 10:03:47

Thanks for your inputs....Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources