SELECT Query that references upon itself 1

[ElEye]

I don't quite know how to express what I'd like to do without providing an example of before/after. Here goes:

How do I turn this:

ID1 ID2 Col_Type Col_Capture
25 14 t.01.id Item_01
25 14 t.01.resp Dr. Robert
25 14 t.01.value Lennon
25 14 t.01.dt 2011-09-01 16:00
26 14 t.07.id Item_05
26 14 t.07.resp Eleanor Rigby
26 14 t.07.value Starr
26 14 t.07.dt 2012-10-15 09:15


into this:
R1 R2 RRef RID R_Response R_Value R_DT
25 14 01 Item_01 Dr. Robert Lennon 2011-09-01 16:00
26 14 07 Item_05 Eleanor Rigby Starr 2012-10-15 09:15


Basically, I'm combining all the results that match t.xx. in the Col_Type column into one new row.

Thank you for any suggestions!

Dave
[

http://flashspot.com

]

 

[feherke]

Hi

[b]select[/b]
id[teal].[/teal]id1 r1[teal],[/teal]
id[teal].[/teal]id2 r2[teal],[/teal]
substring[teal]([/teal]id[teal].[/teal]col_type[teal],[/teal][purple]3[/purple][teal],[/teal][purple]2[/purple][teal])[/teal] rref[teal],[/teal]
id[teal].[/teal]col_capture rid[teal],[/teal]
resp[teal].[/teal]col_capture r_response[teal],[/teal]
value[teal].[/teal]col_capture r_value[teal],[/teal]
dt[teal].[/teal]col_capture r_dt

[b]from[/b] eleye id
[b]inner[/b] [b]join[/b] eleye resp [b]using[/b] [teal]([/teal]id1[teal])[/teal]
[b]inner[/b] [b]join[/b] eleye value [b]using[/b] [teal]([/teal]id1[teal])[/teal]
[b]inner[/b] [b]join[/b] eleye dt [b]using[/b] [teal]([/teal]id1[teal])[/teal]

[b]where[/b] id[teal].[/teal]col_type [b]like[/b] [green][i]'t.%.id'[/i][/green]
[b]and[/b] resp[teal].[/teal]col_type [b]like[/b] [green][i]'t.%.resp'[/i][/green]
[b]and[/b] value[teal].[/teal]col_type [b]like[/b] [green][i]'t.%.value'[/i][/green]
[b]and[/b] dt[teal].[/teal]col_type [b]like[/b] [green][i]'t.%.dt'[/i][/green]

Feherke.

http://free.rootshell.be/~feherke/

 

[ElEye]

Thank you so very much! This does exactly what I needed and it runs very fast!

Dave
[

http://flashspot.com

]