Select Query 1

[Freemo]

Hi,

Due to bad table design (not my design either i am quick to point out!) i am having trouble with a select query. Records are stored in the table as follows:
Histid Eventtype
2 Notes: This is a test note.
2 Type: Free Format

The notes in eventype on the top row relate specifically to the 2nd row. What i want to produce is this:
histid Type Notes
2 Type: Free Format Notes: This is a test note

Is this possible to do without the use of Temp tables?

Thanks & Regards
Justin

 

[bborissov]

SELECT HistId,
       MAX(CASE WHEN LEFT(EventType,4) = 'Type'
                     THEN EventType
                ELSE '' END) AS Type,
       MAX(CASE WHEN LEFT(EventType,5) = 'Notes'
                     THEN EventType
                ELSE '' END) AS Notes
FROM YourTable
GROUP BY HistId

NOT TESTED!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Freemo]

That works an absolute treat!

Many thanks for the quick response.

Justin