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)
 Output Vertical Grid

Author  Topic 

indr4w
Starting Member

27 Posts

Posted - 2013-07-23 : 23:33:56
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

52326 Posts

Posted - 2013-07-24 : 01:28:40
[code]
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)
[/code]

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

indr4w
Starting Member

27 Posts

Posted - 2013-07-24 : 02:17:34
Transact sql is OK, Thanks.

how if used to create view
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-24 : 02:29:06
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-07-24 : 02:29:21
quote:
Originally posted by indr4w

Transact sql is OK, Thanks.

how if used to create view


CREATE VIEW ViewName
AS
--Above SELECT statement

--
Chandu
Go to Top of Page

indr4w
Starting Member

27 Posts

Posted - 2013-07-24 : 03:03:15
I mean create a view using sql server enterprise manager
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-24 : 03:37:43
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
Go to Top of Page

indr4w
Starting Member

27 Posts

Posted - 2013-07-24 : 04:19:04
Ok its work, Thank very much
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-24 : 04:33:11
welcome

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

- Advertisement -