Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 21st, 2003, 01:41 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Default Report query

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.
 
Old July 22nd, 2003, 09:44 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old July 22nd, 2003, 02:21 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Report from Query hajjara Access 1 March 28th, 2008 06:41 AM
Report Query happygv SQL Server 2000 4 June 11th, 2007 08:16 PM
Crystal Report Query asters Crystal Reports 0 December 29th, 2006 10:37 AM
Non query Report Parameter qa BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 0 July 2nd, 2004 02:49 PM
Generating a report from more than one query lic023 Access VBA 1 March 31st, 2004 06:46 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.