Combining multiple rows into one row

Question

Hello everyone.

I have a table containing user-account permissions and I'm trying to write a query to return one row for each user-account combination.

Here is what I have.

CltKey  AcctKey TranTypeID  Access
10      2499    10          0
10      2499    11          1
10      2499    12          1
10      2764    10          1
10      2764    11          1
10      2764    12          0

Here is what I'd like to have.

CltKey  AcctKey TranTypeID1 Access1 TranTypeID2 Access2 TranTypeID3 Access3
10      2499    10          0       11        1       12        1
10      2764    10          1       11        1       12        0

Or even better something like this.

CltKey  AcctKey HasTranTypeID1  HasTranTypeID2 HasTranTypeID3
10      2499    0               1              1
10      2764    1               1              0    

I have tried doing a self join, but I keep getting multiple rows for each TranTypeID. One with it equal to 0 and another with it equal to 1. I have also tried using nested "Select" statements, but the performance is horrible. Does anyone have an idea on how to do this?

Thanks.

Edit: Unfortunately, this has to work in SQL 2000.

Answer 1

It's been a while since I used SQLServer 2000, but this will probably work.

select cltkey, acctkey, 
max( case when trantypeid = 10 and access = 1 
      then 1 else 0 end ) as hastrantypeid1,
max( case when trantypeid = 11 and access = 1 
      then 1 else 0 end ) as hastrantypeid2,
max( case when trantypeid = 12 and access = 1 
      then 1 else 0 end ) as hastrantypeid3
from table
group by cltkey, acctkey;

If not, try this:

create view has_access as 
select cltkey, acctkey, 
max( case when trantypeid = 10 and access = 1 
      then 1 else 0 end ) as hastrantypeid1,
max( case when trantypeid = 11 and access = 1 
      then 1 else 0 end ) as hastrantypeid2,
max( case when trantypeid = 12 and access = 1 
      then 1 else 0 end ) as hastrantypeid3
from table;

and then get your results from this

select cltkey, acctkey, 
max( hastrantypeid1) as hastrantypeid1,
max( hastrantypeid2 ) as hastrantypeid2,
max( hastrantypeid2 ) as hastrantypeid2
from has_access
group by cltkey, acctkey;

Note that this will tell you a (cltkey, acctkey) has access (of a particular type) if any row for that tuple of (cltkey, acctkey) has access for that particular type. That is, it's essentially a row-wise OR.

If all rows for that tuple must have access for that tuple to have access, that is, if you want a row-wise AND, you'll need to do this:

min( case when trantypeid = 10 
      then case when access = 1 
            then 1 else 0 end else null end) as hastrantypeid1,
etc.

Answer 2

SELECT CltKey, AcctKey,
    MAX(CASE TrantypeId WHEN 10 THEN Access ELSE NULL END) AS HasTranTypeID1,
    MAX(CASE TrantypeId WHEN 11 THEN Access ELSE NULL END) AS HasTranTypeID2,
    MAX(CASE TrantypeId WHEN 12 THEN Access ELSE NULL END) AS HasTranTypeID3
FROM PermissionsTable
GROUP BY CltKey, AcctKey
ORDER BY CltKey, AcctKey
;

Answer 3

Use PIVOT - Here is an example: http://msdn.microsoft.com/en-us/library/ms177410.aspx