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

indr4w
Starting Member

27 Posts

Posted - 2013-07-24 : 02:17:34

Transact sql is OK, Thanks.

how if used to create view

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

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

indr4w
Starting Member

27 Posts

Posted - 2013-07-24 : 03:03:15

I mean create a view using sql server enterprise manager

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

indr4w
Starting Member

27 Posts

Posted - 2013-07-24 : 04:19:04

Ok its work, Thank very much

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources