need help with breaking data into multiple columns from a single colum

[ifthenelsenull]

SQL server 2008

I have two tables that contain three columns that I need.

The first table is the form on which data is entered into a UI and gives me the formname, entereddate and lastchangedtime. This table contains thousands of abbreviations of which I only need 'form1'. This table links to another table that contains values for the form:
Abbreviation, value and lastchangedtime. This table contains thousands of abbreviations of which I only need abc123, abc456 and efg123. The values are a varchar (2500) and the lastchangedtime is actually a date and time field.

This is in the middle of a much larger query pulling in employee information like name, department etc.

What I am currently doing is to alias table two and get isolated values like:

--table 1
Formname = H1.Formname,
enteredDT = H1.entereddate,
Lastchangedtime = H1.Lastchangedtime,

--table 2
t1Abbr = t1.abbr,
t1Value = t1.Value,
t1lastcng = t1.lastchangedtime,

--table 3
t2Abbr = t2.abbr,
t2Value = t2.Value,
t2lastcng = t2.lastchangedtime



--Joins look like

LEFT OUTER JOIN Obs t1 with (NOLOCK)
on H1.assessmentid = t1.assessmentid
and t1.FindingAbbr = 'abc123'


LEFT OUTER JOIN Obs t2 with (NOLOCK)
on H1.assessmentid = HO2.assessmentid
and t2.FindingAbbr = 'abc456'


What I want to occur is that I get

Person_Name, department, formname, t1Abbr, t1Value, t1lastcng, t2Abbr, t2Value, t2lastcng etc...

I've tried to figure it out and it can probably be done with a CTE but I'm not aggregating any of the values and I don't know how to filter for the specific abbreviations I'm looking for.

Any help you can provide is appreciated.

 

[markros]

What you're looking for is the typical PIVOT query. You need to use MAX (or MIN function) constructing your PIVOT.

PluralSight Learning Library

 

[ifthenelsenull]

But can I only pivot half a query? Those tables are only part of it .

 

[markros]

I think you need to restructure your query a bit

;with cte as (select .., row_number() over (partition by H.AssessmentID order by t1.Abbr) as Rn
LEFT JOIN Obs t1 on t1.AssessmentID = H1.AssessmentID)

select AssessmentID, .., 
max(case when Rn =1 then t1.Abbr end) as Abbr1,
max(case when Rn =1 then t1.Value end) as Value1,
etc.
from cte
GROUP BY AssessmentID

PluralSight Learning Library

 

[ifthenelsenull]

Maybe I'm missing something obvious in this. If so please pardon me I'm new. Where in the CTE do I indicate that t1.findingabbr = 'abc123' and t2.finding abbr = 'abc456'?

I need to parse those values from the other ones to get it to display correctly.

 

[markros]

In your message above both JOINs were with the same table.

Can you post your current query so I can tell you?

Thanks.

PluralSight Learning Library