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 Table (UserID INT, UserName VARCHAR(50), billed BIT, grossamt MONEY)

  INSERT INTO @Test (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

  GROUP BY UserID

  ,UserName