t-sql question - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

t-sql question

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-03-31 : 12:51:53

I have the following sample data:

DECLARE @t TABLE (
Ord_refil1 VARCHAR(15),
ord_refil2 VARCHAR(15),
ord_refil3 VARCHAR(15),
online_ord1 SMALLINT,
online_ord2 SMALLINT,
online_ord3 SMALLINT,
loc VARCHAR(15)
);

INSERT INTO @t VALUES('med1','med2','med3',0,1,0,'loc1');
INSERT INTO @t VALUES('med1',null,null,0,1,0,'loc2');
INSERT INTO @t VALUES(null,'med2','med3',0,0,0,'loc1');
INSERT INTO @t VALUES(null,'med2',null,1,0,0,'loc3');
INSERT INTO @t VALUES(null,'med2',null,0,0,0,'loc4');

SELECT * FROM @t

I have 15 order_refill columns like Order_refill1,Order_refill2,Order_refill3......Order_refill15.
In the similar way I have 15 online_order columns like online_order1,online_order2,online_order3.....online_order15.
In the above data: the values for online_orders mean:
0 is non-onlineorder; 1 is onlineorder

I need to get the following output from the above data, should display the counts of online_orders and
non-onlineorders for each location based on order_refills, we should count only where order_refill column is not null

Location Online_Order Non-OnlineOrder
loc1 1 4
loc2 1 0
loc3 1 0
loc4 0 1

Thanks for all your help.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-31 : 12:58:31

1st thing you should do is get your data into First Normal Form.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-31 : 13:07:05

[code]
select loc,
sum(case when orderval=1 then 1 else 0 end) as [Online_Order],
sum(case when orderval=0 then 1 else 0 end) as [Non-OnlineOrder]
from @t
unpivot (orderval for orders in ([online_ord1],[online_ord2], [online_ord3],..., [online_ord15]))u
group by loc
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources