How do I span my columns horizontally ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How do I span my columns horizontally ?

Author

Topic

eurob
Posting Yak Master

100 Posts

Posted - 2006-06-27 : 16:01:37

This is the output that I need:

Client--------Jan-------Feb
Ford----------100-------200
Honda---------50--------75

The code below doesn't work because it needs to be grouped by b.auditid
But I want to group it by client...
I hope I am clear enough...

select 
clientname,
(select count(a.auditid) from audits a
where a.auditid = b.auditid and date between '1-1' and '2-1'),
(select count(a.auditid) from audits a
where a.auditid = b.auditid and date between '2-1' and '3-1')
from audits b
group by clientname

robert

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 16:06:01

Something like this?

SELECT		ClientName,
		SUM(CASE WHEN MONTH(Date) = 1 THEN 1 ELSE 0 END) 'Jan',	
		SUM(CASE WHEN MONTH(Date) = 2 THEN 1 ELSE 0 END) 'Feb'
FROM		Audits
WHERE		YEAR(Date) = 2006
GROUP BY 	ClientName

Peter Larsson
Helsingborg, Sweden

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-28 : 01:14:12

Robert,

You code would be fine, if you joined tables a and b on clientname, not on auditid. After all, you're not wanting to count how many rows there are when you know auditid, you're wanting to know how many rows there are when you know clientname.

But Peso's query will work better. If you're using SQL2005, you could do it in an even better way, using the PIVOT command.

Rob Farley
http://robfarley.blogspot.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-28 : 02:30:25

http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

eurob
Posting Yak Master

100 Posts

Posted - 2006-06-28 : 08:45:07

Peter, when I tried your approach e.g:

SUM(CASE WHEN MONTH(Date) = 1 THEN 1 ELSE 0 END) 'Jan'

etc. it doesn't recognize the month correctly because it will show
the full count for the whole time period. I will play with it.
Thanks.

robert

eurob
Posting Yak Master

100 Posts

Posted - 2006-06-28 : 08:53:32

I noticed it does work with SUM, but not with count.

robert

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-28 : 08:57:42

If you want to use count use

COUNT(CASE WHEN MONTH(Date) = 1 THEN 1 END) 'Jan'

Madhivanan

Failing to plan is Planning to fail

eurob
Posting Yak Master

100 Posts

Posted - 2006-06-28 : 09:02:00

That is exactly what I tried.


select COUNT(CASE WHEN MONTH(Date) = 1 THEN 1 END) 'Jan'
where date between '1-1-2006' and '2-1-2006'

shows 500 records


select COUNT(CASE WHEN MONTH(Date) = 1 THEN 1 END) 'Jan'
where date between '1-1-2006' and '5-1-2006'

shows 2000 records

So it doesn't work correctly with count.

robert

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-28 : 09:04:24

You should use yyyymmdd format to avoid conflict with local settings

Try

select COUNT(CASE WHEN MONTH(Date) = 1 THEN 1 END) 'Jan'
where date between '2006-01-01' and '2006-02-01'

select COUNT(CASE WHEN MONTH(Date) = 1 THEN 1 END) 'Jan'
where date between '2006-01-01' and '2006-05-01'

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 09:10:34

quote:
Originally posted by eurob

I noticed it does work with SUM, but not with count.

robert

There is no difference summing up 1+1+1+1 or counting 1 four times.

Peter Larsson
Helsingborg, Sweden

eurob
Posting Yak Master

100 Posts

Posted - 2006-06-28 : 09:10:56

I tried that but that is not the issue I think since it does work fine with SUM. What I notice is even when I change the case clause it stil shows all records, e.g


select 
count(case when MONTH(auditDate) = 1 then 0 else 0 end) 'Jan'
from audits where auditdate between '2006-01-01' and '2006-05-02'

Shows all records which doesn't make sense to me, it seems that the CASE is completely ignored.

robert

eurob
Posting Yak Master

100 Posts

Posted - 2006-06-28 : 09:12:50

Peter there is, I need to count records.
So I count on auditids which are like 100,101,102,256 etc.. then a sum vs count shows a big difference.

robert

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 09:13:00

quote:
Originally posted by eurob

That is exactly what I tried.
select COUNT(CASE WHEN MONTH(Date) = 1 THEN 1 END) 'Jan'
where date between '1-1-2006' and '2-1-2006'
shows 500 records
select COUNT(CASE WHEN MONTH(Date) = 1 THEN 1 END) 'Jan'
where date between '1-1-2006' and '5-1-2006'
shows 2000 records

So it doesn't work correctly with count.

It is not COUNT that is not working. You are selecting dates between January 1, 2006 to May 1, 2006, hence the larger number when counting.

Try using SET DATEFORMAT DMY if worried.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 09:15:56

quote:
Originally posted by eurob

Peter there is, I need to count records.
So I count on auditids which are like 100,101,102,256 etc.. then a sum vs count shows a big difference.

Try out with both methods.
You are not summing up 100 + 101 + 102 + 256! You are summing up 1 (if date is in correct month, 0 if otherwise) for every AuditID there is.

If you not are trying to count distinct AuditID?

Peter Larsson
Helsingborg, Sweden

eurob
Posting Yak Master

100 Posts

Posted - 2006-06-28 : 09:20:39

Peter,

You are correct, it works.


Try. You are not summing up 100 + 101 + 102 + 256. You are summing up 1 and 0 if date is in correct month for every AuditID.

Thanks all.
Still wonder why count doesn't work with that CASE but that's ok.

robert

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 09:21:58

quote:
Originally posted by eurob

Peter,

You are correct, it works.

Thanks all.
Still wonder why count doesn't work with that CASE but that's ok.

COUNT of 0, 0, 1, 1, 1, 0, 0, NULL is 7, with warning message.
 SUM  of 0, 0, 1, 1, 1, 0, 0, 0    is 3.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-28 : 09:24:17

quote:
Originally posted by eurob

I tried that but that is not the issue I think since it does work fine with SUM. What I notice is even when I change the case clause it stil shows all records, e.g

select
count(case when MONTH(auditDate) = 1 then 0 else 0 end) 'Jan'
from audits where auditdate between '2006-01-01' and '2006-05-02'

Shows all records which doesn't make sense to me, it seems that the CASE is completely ignored.

robert

It seems you didnt read my reply fully

That should be

select
count(case when MONTH(auditDate) = 1 then 0 end) 'Jan'
from audits where auditdate between '2006-01-01' and '2006-05-02'

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 09:25:36

quote:
Originally posted by eurob

I tried that but that is not the issue I think since it does work fine with SUM. What I notice is even when I change the case clause it stil shows all records, e.g
select
count(case when MONTH(auditDate) = 1 then 0 else 0 end) 'Jan'
from audits where auditdate between '2006-01-01' and '2006-05-02'
Shows all records which doesn't make sense to me, it seems that the CASE is completely ignored.

robert

Skip ELSE 0 and it should work with count too!

select 
count(case when MONTH(auditDate) = 1 then 99 else 0 end) 'Jan'
from audits where auditdate between '2006-01-01' and '2006-05-02'

But there is one drawback counting NULLs. SQL Server will produce the warning Warning: Null value is eliminated by an aggregate or other SET operation., and depending on your error handling in client/front-end application, this can cause unpredicted results.

Peter Larsson
Helsingborg, Sweden

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-28 : 20:51:25

Yes - the feature here for using count is that it will ignore NULLs. I would stick to SUM() myself. Of course, you can avoid sum if you want, by using the Rozenshtein Method.

SUM(1-ABS(SIGN(MONTH(auditDate)-2))) AS Feb,
SUM(1-ABS(SIGN(MONTH(auditDate)-3))) AS Mar,
SUM(1-ABS(SIGN(MONTH(auditDate)-4))) AS Apr

The reason behind this is that SIGN(n) gives 0 for 0, -1 for negative numbers and 1 for positives. So ABS(SIGN(x-y)) gives 0 for 'x=y' and 1 for 'x!=y'. So 1-ABS(SIGN(x-y)) gives 1 for 'match' and 0 for 'nomatch', and that works for creating a sum.

Rob Farley
http://robfarley.blogspot.com

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-28 : 21:17:23

You can use a date table.


select 
	a.clientname,
	[Jan] = sum(case b.[MONTH] when 1 then 1 else 0 end) ,
	[Feb] = sum(case b.[MONTH] when 2 then 1 else 0 end) ,
	[Mar] = sum(case b.[MONTH] when 3 then 1 else 0 end) ,
	[Apr] = sum(case b.[MONTH] when 4 then 1 else 0 end) ,
	[May] = sum(case b.[MONTH] when 5 then 1 else 0 end) ,
	[Jun] = sum(case b.[MONTH] when 6 then 1 else 0 end) ,
	[Jul] = sum(case b.[MONTH] when 7 then 1 else 0 end) ,
	[Aug] = sum(case b.[MONTH] when 8 then 1 else 0 end) ,
	[Sep] = sum(case b.[MONTH] when 9 then 1 else 0 end) ,
	[Oct] = sum(case b.[MONTH] when 10 then 1 else 0 end) ,
	[Nov] = sum(case b.[MONTH] when 11 then 1 else 0 end) ,
	[Dec] = sum(case b.[MONTH] when 12 then 1 else 0 end) 
from
	audits a
	join
	-- Function in Script Library forum
	F_TABLE_DATE('20060101','20061231') b
	on a.[DATE] = b.[DATE]
group by
	a.clientname
order by
	a.clientname

Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

CODO ERGO SUM

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-28 : 21:26:13

Ew - that's nasty. And besides, you'd have to truncate them first, or else join on "a.date between b.date and dateadd(day,1,b.date)"

Rob Farley
http://robfarley.blogspot.com

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources