Report query

khautinh · July 21st, 2003, 01:41 PM

I have table1 with three columns and data

UserLogin SysId Clientid
---------- ------ --------
userlogin1 1 12345
userlogin2 4 23455
userlogin2 5 76435
userlogin3 3 22222
userlogin3 3 22222
userlogin4 6 55467

I'd like to write a query that will have the output

UserLogin 1 2 3 4 5 6 7 8
--------- --- --- --- --- --- --- --- ---
userlogin1 1 0 0 0 0 0 0 0
userlogin2 0 0 0 1 1 0 0 0
userlogin3 0 0 2 0 0 0 0 0
userlogin4 0 0 0 0 0 1 0 0

I have 8 systems and userlogin is a dynamic data. Any help would be profoundly appreciated.

pgtips · July 22nd, 2003, 09:44 AM

Here is a way to do this using a UDF (hope you have SQL 2K!):

Code:

CREATE FUNCTION dbo.ForSumIf (@FieldValue INT, @CheckFor INT)
RETURNS INT
AS
BEGIN
    DECLARE @ret INT
    IF (@FieldValue = @CheckFor)
        SET @ret = 1
    ELSE
        SET @ret = 0

    RETURN @ret
END



SELECT UserLogin, 
    SUM(dbo.ForSumIf(SysId, 1)) AS [1],
    SUM(dbo.ForSumIf(SysId, 2)) AS [2],
    SUM(dbo.ForSumIf(SysId, 3)) AS [3],
    SUM(dbo.ForSumIf(SysId, 4)) AS [4],
    SUM(dbo.ForSumIf(SysId, 5)) AS [5],
    SUM(dbo.ForSumIf(SysId, 6)) AS [6],
    SUM(dbo.ForSumIf(SysId, 7)) AS [7],
    SUM(dbo.ForSumIf(SysId, 8)) AS [8]
FROM <<YourTable>>
GROUP BY UserLogin

Not sure how efficient this method is, I'm sure someone else here can enlighten you on that (if you're bothered about it :))

hth
Phil

Jeff Mason · July 22nd, 2003, 02:21 PM

What you are looking for is called a cross-tab or pivot table query. Unfortunately, SQL Server does not have a simple way to handle this sort of thing. In this (one thing, IMO) Access has an advantage, as it provides a syntax to accomplish this in a more or less straightforward manner.

However, you can accomplish this in SQL Server using the CASE expression, as long as you know ahead of time how many columns will be involved.

Since you have 8 Sysid's, you do. Now it's just a matter of combining 8 CASE expressions (one for each system) as:

Code:

SELECT UserLogin,
        SUM(CASE WHEN SysId=1 THEN 1 ELSE 0 END) as '1'
        SUM(CASE WHEN SysId=2 THEN 1 ELSE 0 END) as '2'
        SUM(CASE WHEN SysId=3 THEN 1 ELSE 0 END) as '3'
        SUM(CASE WHEN SysId=4 THEN 1 ELSE 0 END) as '4'
        SUM(CASE WHEN SysId=5 THEN 1 ELSE 0 END) as '5'
        SUM(CASE WHEN SysId=6 THEN 1 ELSE 0 END) as '6'
        SUM(CASE WHEN SysId=7 THEN 1 ELSE 0 END) as '7'
        SUM(CASE WHEN SysId=8 THEN 1 ELSE 0 END) as '8'
    FROM YourTable
GROUP BY UserLogin
ORDER BY UserLogin;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com