Another row to col issue

Hi,

I've got a Problem to craete a view which works like this:

I've got this Table:

USR_ID ATNAM ATVAL
-- -
2 USR_DEPARTMENT BS1
2 USR_MARITAL_STATE married
3 USR_DEPARTMENT BS1
3 USR_MARITAL_STATE single

and I have to display it in a view like this

USR_ID USR_DEPARTMENT USR_MARITAL_STATE
- - -
2 BS1 married
3 BS1 single

maybe there will be additional "ATNAMS" in future.

Has anybode an idea how i can achieve this?

thanks in advance

Raimund

Is there a reason that you cannot use PIVOT? Are you using SQL Server 2000 or SQL Server 2005?

With SQL Server 2005 you can create a view based on a pivot; it might look something like:

Code Snippet

create view dbo.aView
as
select USR_ID,
[USR_DEPARTMENT],
[USR_MARITAL_STATE]
from source
pivot( max(ATVAL) for ATNAM
in([USR_DEPARTMENT],[USR_MARITAL_STATE])
) pv

The MAX / CASE construct can be used either with SQL 2000 or SQL 2005; that version might look something like:

Code Snippet

create view dbo.aView
as

select USR_ID,
max( case when ATNAM = 'USR_DEPARTMENT'
then ATVAL end)
as USR_MARITAL_STATE,
max( case when ATNAM = 'USR_MARITAL_STATE'
then ATVAL end)
as USR_MARITAL_STATE
from source
group by USR_ID

|||

I just didn't know about the trick with te max statement used as aggregate function.

For dynamic adaption for new "ATNAMS" I'll try it with a trigger on the according definition table.

Thanks for fast reply

Raimund