Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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.
Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 08:42 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.