SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Output Vertical Grid
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

indr4w
Starting Member

Indonesia
27 Posts

Posted - 07/23/2013 :  23:33:56  Show Profile  Reply with Quote
Hello, Mr Visakh

I have 2 table

1. stock_in
Norec codeitem itemName qty date unit
----------------------------------------------------
124 AK001SL TUBE AIR 500 2013/05/01 Pcs
125 AK001SL TUBE AIR 200 2013/05/02 Pcs
126 AK001SL TUBE AIR 100 2013/05/03 Pcs
127 AK001SL TUBE AIR 200 2013/05/05 Pcs
124 AOP-020 WEATHERSTIP 100 2013/05/01 Pcs
125 AOP-020 WEATHERSTIP 200 2013/05/03 Pcs

2. Mproduct
CodeItem ItemName PartNo
-------------------------------------------------
AK001SL TUBE AIR AA2ACL-KTUBVBBK01
AOP-020 WEATHERSTIP 67881-BZ150-K

How made output this

CodeItem ItemName [1] [2] [3] [4] [5]..[31]
----------------------------------------------------------------------
AK001SL TUBE AIR 500 200 100 200
AOP-020 WEATHERSTIP 100 200
----------------------------------------------------------------------
Thanks.

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/24/2013 :  01:28:40  Show Profile  Reply with Quote

SELECT CodeItem,ItemName,
SUM(CASE WHEN DAY(date) = 1 THEN qty ELSE 0 END) AS [1],
SUM(CASE WHEN DAY(date) = 2 THEN qty ELSE 0 END) AS [2],
SUM(CASE WHEN DAY(date) = 3 THEN qty ELSE 0 END) AS [3],
SUM(CASE WHEN DAY(date) = 4 THEN qty ELSE 0 END) AS [4],
SUM(CASE WHEN DAY(date) = 5 THEN qty ELSE 0 END) AS [5],
...
SUM(CASE WHEN DAY(date) = 31 THEN qty ELSE 0 END) AS [31]
FROM Stock_in 
GROUP BY CodeItem,ItemName,DATEADD(mm,DATEDIFF(mm,0,[date]),0)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 07/24/2013 :  02:17:34  Show Profile  Reply with Quote
Transact sql is OK, Thanks.

how if used to create view
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/24/2013 :  02:29:06  Show Profile  Reply with Quote
same way

CREATE VIEW MonthlyMatrix
AS
SELECT CodeItem,ItemName,
SUM(CASE WHEN DAY(date) = 1 THEN qty ELSE 0 END) AS [1],
SUM(CASE WHEN DAY(date) = 2 THEN qty ELSE 0 END) AS [2],
SUM(CASE WHEN DAY(date) = 3 THEN qty ELSE 0 END) AS [3],
SUM(CASE WHEN DAY(date) = 4 THEN qty ELSE 0 END) AS [4],
SUM(CASE WHEN DAY(date) = 5 THEN qty ELSE 0 END) AS [5],
...
SUM(CASE WHEN DAY(date) = 31 THEN qty ELSE 0 END) AS [31]
FROM Stock_in 
GROUP BY CodeItem,ItemName,DATEADD(mm,DATEDIFF(mm,0,[date]),0)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 07/24/2013 :  02:29:21  Show Profile  Reply with Quote
quote:
Originally posted by indr4w

Transact sql is OK, Thanks.

how if used to create view


CREATE VIEW ViewName
AS
--Above SELECT statement

--
Chandu

Edited by - bandi on 07/24/2013 02:30:00
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 07/24/2013 :  03:03:15  Show Profile  Reply with Quote
I mean create a view using sql server enterprise manager
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/24/2013 :  03:37:43  Show Profile  Reply with Quote
quote:
Originally posted by indr4w

I mean create a view using sql server enterprise manager


dont use enterprise manager editor

Go to query analyser open a new window after connecting to server and database and type the given query and execute

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 07/24/2013 03:38:13
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 07/24/2013 :  04:19:04  Show Profile  Reply with Quote
Ok its work, Thank very much
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/24/2013 :  04:33:11  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000