advanced query help needed

I have 2 tables
mvm_campi
id_camp dsc_cam
1 desc1
2 desc2
3 desc3
mvm_prova
id_prova id_camp desc
1 2 x
2 2 y
3 2 z
3 1 k
Is there a way to get an SQL query result as following
idcamp(1) idcamp(2)
desc1 desc2
idprova(1) null x
2 null y
3 k zTry,
select
a.id_prova,
min(case when b.dsc_cam = 'desc1' then a.col_desc end) as desc1,
min(case when b.dsc_cam = 'desc2' then a.col_desc end) as desc2
from
mvm_prova as a
inner join
mvm_campi as b
on a.id_camp = b.id_camp
group by
a.id_prova
order by
a.id_prova
go
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
AMB
"Denis" wrote:

> I have 2 tables
> mvm_campi
> id_camp dsc_cam
> 1 desc1
> 2 desc2
> 3 desc3
> mvm_prova
> id_prova id_camp desc
> 1 2 x
> 2 2 y
> 3 2 z
> 3 1 k
> Is there a way to get an SQL query result as following
> idcamp(1) idcamp(2)
> desc1 desc2
> idprova(1) null x
> 2 null y
> 3 k z
>
>
>|||Your query doesn't work because
it has 'desc1' an 'desc2' static, but dsc_cam of table mvm_campi is dynamic
(inserted by the user)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> ha scritto nel
messaggio news:CE3D53F3-4664-4739-96EF-70552A14A037@.microsoft.com...
> Try,
> select
> a.id_prova,
> min(case when b.dsc_cam = 'desc1' then a.col_desc end) as desc1,
> min(case when b.dsc_cam = 'desc2' then a.col_desc end) as desc2
> from
> mvm_prova as a
> inner join
> mvm_campi as b
> on a.id_camp = b.id_camp
> group by
> a.id_prova
> order by
> a.id_prova
> go
> HOW TO: Rotate a Table in SQL Server
> http://support.microsoft.com/defaul...574&Product=sql
>
> AMB
>
> "Denis" wrote:
>
>|||Dynamic Crosstab Queries
http://www.windowsitpro.com/SQLServ...5608/15608.html
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
AMB
"Denis" wrote:

> Your query doesn't work because
> it has 'desc1' an 'desc2' static, but dsc_cam of table mvm_campi is dynami
c
> (inserted by the user)
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> ha scritto nel
> messaggio news:CE3D53F3-4664-4739-96EF-70552A14A037@.microsoft.com...
>
>|||Do you have something against making things (ie. dynamic pivoting) as simple
as
possible for the user? :)
Check out the RAC utility for S2k.
www.rac4sql.net
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:DFA21DFD-A83E-453B-8311-9497C5D333E4@.microsoft.com...
> Dynamic Crosstab Queries
> http://www.windowsitpro.com/SQLServ...5608/15608.html
> Dynamic Cross-Tabs/Pivot Tables
> http://www.sqlteam.com/item.asp?ItemID=2955
>
> AMB
> "Denis" wrote:
>