How to get single record results 1

[Crystalyzer]

I have two tables as follows:

Contacts:
ID Name
1 John
2 Mary
3 Joe
4 Sue

Addfields:
ID fieldname fieldvalue
1 Exp Date 12/31/05
1 Priority High
2 Exp Date 1/31/06
2 Priority Low

When I simply join the two tables on ID I get multiple records back for contacts 1 & 2 which I understand is not "wrong", however, I want to get a single row per contact with the fieldnames from Addfields as columns in my result (Exp Date and Priority) and their values listed in those columns.

Is this possible? I'm stumped.

Thanks in advance for any direction anyone can give!

Thanks and best regards,
-Lloyd

 

[micha123]

If you have specific list of column names (for example 5 column names) there , then I would suggest:

SELECT c.ID,c.name,
       a1.fieldvalue AS 'Exp Date'  ,
       a2.fieldvalue AS 'Priority'  ,
       a3.fieldvalue AS 'Whatever1' ,
       a4.fieldvalue AS 'Whatever2' ,
       a5.fieldvalue AS 'Whatever3'
FROM Contacts c 
     outer join Addfields a1 ON a1.ID = c.ID and a1.fieldname = 'Exp Date' 
     outer join Addfields a2 ON a2.ID = c.ID and a2.fieldname = 'Priority' 
     outer join Addfields a3 ON a3.ID = c.ID and a2.fieldname = 'Whatever1' 
     outer join Addfields a4 ON a4.ID = c.ID and a3.fieldname = 'Whatever2'
     outer join Addfields a5 ON a5.ID = c.ID and a3.fieldname = 'Whatever3'

OTHERWISE - I think you would need to use a CURSOR
(which can degradate your query performance significally!)

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[Crystalyzer]

micha123

Thanks so much for your suggestion! It worked like a charm!!!

Star for you and my undying gratitude!!



Thanks and best regards,
-Lloyd

 

[micha123]

You made my day!







"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[amrita418]

Or you can use a case function to do that:

select   c.id, 
         c.name, 
         case when b.fieldname = 'Exp Date' then b.fieldvalue else NULL end 'Exp Date', 
         case when b.fieldname = 'Priority' then b.fieldvalue else NULL end 'Priority' 
from     contacts c inner join AddFields 
on       (a.id = b.id)

PS: Code not Tested.

Regards,
AA

 

[micha123]

I think this would not return the result set he's expecting...


"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[amrita418]

Oops my bad, I overlooked the distinct record part.

Adding a max function to the case stmt and group by clause should do it though.

Something like this:

select   c.id, 
         c.name, 
         max(case when b.fieldname = 'Exp Date' then b.fieldvalue else '01/01/1900' end) 'Exp Date', 
         max(case when b.fieldname = 'Priority' then b.fieldvalue else 'L' end) 'Priority' 
from     contacts c inner join AddFields 
on       (a.id = b.id)
group by c.id, c.name

I am not around an sql server machine to test my code.

Regards,
AA

 

[carterlj]

How would you do this if:

Project
01 project 01
02 project 02
03 project 03

Project_State
01 01
01 02
02 01
02 02

Policy_state
01 Alabama
02 Arizona

To get
project 01 Alabama Arizona
project 02 Alabama Arizona

 

[micha123]

A question first: can you define the maximal number of states for a project, or is it dynamic?

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[carterlj]

It is dynamic (can vary from 1 to 50)

 

[micha123]

It is a little complicated but ...

DECLARE @rc int, @err int

-- CREATE A TEMPORARY TABLE WITH ALL PROJECTS AND STATE NAMES + SEQUENTIAL #:
SELECT RecID = identity(int,1,1),
       p.ProjectID,
       prst.StateName
INTO #all
FROM Projects p 
             left join Project_State prst
                         ON p.ProjectID = prst.ProjectID
             left join Policy_state post
                         ON prst.StateID = prst.StateID
order by p.ProjectID, prst.StateName

-- CREATE ANOTHER TEMP TABLE WITH MINIMUM RecID for a project:
SELECT ProjectID, min(RecID) as MinRecID
INTO #min 
FROM #all
group by ProjectID

SELECT a.ProjectID, a.RecID,
       a.RecID - m.MinRecID + 1 as SubRecID,  -- A sub SeqID for each project
       a.StateName
into #last
from #all a inner join #min m on a.ProjectID = m.ProjectID
order by a.RecID

-- OPTIONAL, BUT CLEARS SPACE IN TEMPDB:
drop table #all
drop table #min

/* NOW WE HAVE IN #last:
RecID   projectID   SubRecID       State
-------------------------------------------
1          01          1           Alabama
2          01          2           Arizona
3          01          3           Carolina
4          02          1           Alabama
5          02          2           Mississipi
6          03          1           Dallas
...
*/

create table #States4project
(ProjectID char(2), 
 State1   varchar(100),
 State2   varchar(100),
 State3   varchar(100),
......
 State50   varchar(100)
)

-- INSERT ProjectID:
insert into #States4Project (ProjectID)
select ProjectID from Projects

-- AND NOW UPDATE ALL STATES IN LOOP:
set @rc = 1
while @rc > 0 and @err = 0
begin
      EXEC ('UPDATE #States4project' +
            ' SET State' + convert(varchar(2),@rc) + ' =' +
            ' l.StateName' +
            ' FROM  #States4project s, #last l' +
            ' WHERE  s.ProjectID = l.ProjectID' +
            '   AND  l.SubRecID = ' + convert(varchar(2),@rc) )

     set @rc = @@rowcount, @err = @@error
  
end

if @err = 0
     select * from #States4project
GO

I HAVN'T TESTED THIS MONSTER....


"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[carterlj]

Thanks!

I'll give it a try!