Format Query Results

• Erin.

  SSCertifiable

  Points: 5237

  More actions

  January 20, 2009 at 10:24 am

  #196304

  I am using the following code

  SELECT userid, username, billed,SUM(grossamt)

  FROM EMNCommon

  WHERE (transdate BETWEEN '2008-01-01 00:00:00' AND '2008-11-30 23:59:59') AND (transtype = 1) AND (general1 NOT LIKE '%scan%') AND (account LIKE '09999%')

  GROUP BY userid, username, billed

  union

  SELECT userid, username, billed, SUM(grossamt)

  FROM EMNCommonArchive

  WHERE (transdate BETWEEN '2008-01-01 00:00:00' AND '2008-11-30 23:59:59') AND (transtype = 1) AND (general1 NOT LIKE '%scan%')

  GROUP BY userid, username, billed

  To get these results

  ID NAME BILLED AMT

  30681Smith Jane01526.40

  30681Smith Jane115.40

  42511Doe John 1878.00

  42511Doe John 0507.40

  I would like the results to look like this.

  ID NAME NONBILLABLE BILLABLE

  30681Smith Jane1526.40 15.40

  42511Doe John 507.40 878.00

• null-512060

  SSChasing Mays

  Points: 613

  More actions

  January 20, 2009 at 12:03 pm

  #929878

  Please try:

  DECLARE @test-2 Table (UserID INT, UserName VARCHAR(50), billed BIT, grossamt MONEY)

  INSERT INTO @test-2 (UserID, UserName, billed, grossamt)

  SELECT 30681,'Smith Jane',0,1500.40 UNION ALL

  SELECT 30681,'Smith Jane',0,26.00 UNION ALL

  SELECT 30681,'Smith Jane',1,15.40 UNION ALL

  SELECT 42511,'Doe John',1,878.00 UNION ALL

  SELECT 42511,'Doe John',0,507.40

  SELECT UserID

  ,UserName

  ,SUM(CASE WHEN Billed = 0 THEN grossamt ELSE 0 END) as 'NONBILLABLE'

  ,SUM(CASE WHEN Billed = 1 THEN grossamt ELSE 0 END) as 'BILLABLE'

  FROM @test-2

  GROUP BY UserID

  ,UserName