Create Piviot table--Almost - 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

SQL Server Development (2000)

Create Piviot table--Almost

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-02 : 12:33:26

I need to create a table from an SP.

1st Column to be RefDate - Datetime.
The rest of the columns need to come from a partslist table.
SELECT Stock_Item
FROM dbo.Master_Stock_Item

Then I need to add a row where the data in the select will populate the table based on a month/year.

Select Stock_Item,Sum(OrderQty)
From dbo.Sales
where month(orderdate) = month(@RefDate) and
Year(orderdate) = Year(@RefDate)
Group by Stock_Item

Any Ideas

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-02 : 14:45:25

I'm not to clear on what you need, but it sounds like a CROSS JOIN to a "Dates" table....

Can you post the DDL of the Final Destination table?

You know the drill soldier....DDL, DML, expected results...

Now drop and give'em

Brett

8-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-02 : 15:17:49

This would be limited to 8000 characters of dynamic sql, maybe that is enough ?

USE Northwind
GO

DECLARE @ddl_insert VARCHAR(8000); SET @ddl_insert = ''

DECLARE @RefDate DATETIME
SELECT @RefDate = OrderDate FROM Orders ORDER BY NEWID()

SELECT	@ddl_insert = @ddl_insert + ',' + CONVERT(VARCHAR(20),SUM(Freight)) + ' AS ' + CustomerId
FROM	Orders
WHERE	YEAR(OrderDate) = YEAR(@RefDate)
	AND MONTH(OrderDate) = MONTH(@RefDate)
GROUP BY CustomerId
ORDER BY CustomerID


SET @ddl_insert = 'SELECT ''' + CONVERT(VARCHAR(23),@RefDate,121) + ''' AS RefDate' + @ddl_insert + ' INTO Northwind.dbo.TheTable'

-- PRINT @ddl_insert
EXEC(@ddl_insert)
GO

SELECT * FROM Northwind.dbo.TheTable
GO

DROP TABLE Northwind.dbo.TheTable
GO

rockmoose

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-02 : 16:22:33

Ok brett

Expected Table structure
CREATE TABLE dbo.StockCycleCount
Refdate date/time not null
"nameofitem1" int null
"nameofitem2" int null
"nameofitem3" int null
...
...
"nameofitem195" int null

Currently 195 items but will grow

Ist the table is created as above
Then populated from this table

Create dbo.sales
SalesID indentity (1,1)
Orderdate date/time
OrderQty int not null
Stock_item Varchar(20) not null

with this rule
Insert into dbo.StockCycleCount
("nameofitem" for corisponding Stockitem)
Select Sum(OrderQty)
From dbo.Sales
where month(orderdate) = month(@RefDate) and
Year(orderdate) = Year(@RefDate)
Group by Stock_Item

Here is the Poop once the table is created we will manually back enter data from our old sales history.
as we only have one year on of the current detail records and we need three years for the cycle count.
This comes from a month by item report.

Then once a month we will add a new row to this table and remove the oldest Refdate.

In short this is a tempoary fix to automate a manual cycle count.

And this needs to be output in this format

Jim
Users <> Logic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-02 : 16:39:18

Hey Rock

Whats the reason for

quote:
SELECT @RefDate = OrderDate FROM Orders ORDER BY NEWID()

Jim
Users <> Logic

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-02 : 16:42:45

It was just to get a random date from the Orders table.
But now that I reread Your spec I think I have some more work to do...

rockmoose

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-02 : 16:50:28

Thats what I thought but Dynamic SQL is bran new to me.
Better to ask.

This seems to be a good start.

I wonder if I could just make a temp table using this same procidure
then insert it into the existing?

Jim
Users <> Logic

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-02 : 17:05:08

Yes,
but You would still have to build the insert statement from the temporary table.
Here'e what I'm working on right now:

USE Northwind
GO
-- Create the table
CREATE TABLE CycleCount(Refdate DATETIME NOT NULL)
GO

-- Add any new columns to the table
DECLARE @ddl_table VARCHAR(1000)
DECLARE @new_col SYSNAME

WHILE 1=1
BEGIN
	SET @new_col =
	(
		SELECT TOP 1 CustomerId
		FROM 	Customers
		WHERE 	CustomerId NOT IN(
				SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_NAME = 'CycleCount' )
		ORDER BY CustomerId
	)
	IF @new_col IS NULL
		BREAK
	SET @ddl_table = 'ALTER TABLE CycleCount ADD [' + @new_col + '] INT NULL'
	PRINT @ddl_table
	EXEC(@ddl_table)
END
GO

-- Build dynamic sql to insert monthly values for a refdate
DECLARE @ddl_insert VARCHAR(8000); SET @ddl_insert = ''
DECLARE @insert_values VARCHAR(8000); SET @insert_values = ''
DECLARE @RefDate DATETIME

SELECT @RefDate = OrderDate FROM Orders ORDER BY NEWID()

SELECT
	@ddl_insert = @ddl_insert + ',[' + CustomerID + ']'
	,@insert_values = @insert_values + ',' + CONVERT(VARCHAR(20),SUM(Freight))
FROM	Orders
WHERE	YEAR(OrderDate) = YEAR(@RefDate)
	AND MONTH(OrderDate) = MONTH(@RefDate)
GROUP BY CustomerId
ORDER BY CustomerID

SELECT
	@ddl_insert = 'INSERT CycleCount(Refdate' + @ddl_insert + ')'
	,@insert_values = ' VALUES(''' + CONVERT(VARCHAR(23),@RefDate,121) + '''' + @insert_values + ')'

EXEC(@ddl_insert + @insert_values)
GO

SELECT * FROM CycleCount

DROP TABLE CycleCount
GO

rockmoose

Edit: Finished the dynamic code example

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-03 : 07:52:55

This looks great rock!!!!

I will give it a try shortly.
Dang meetings!!!!!!!!

Jim
Users <> Logic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-03 : 08:08:50

hey Jim, have you looked at rob's or jeff's cross tab sproc?

Go with the flow & have fun! Else fight the flow

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-03 : 10:26:58

quote:
hey Jim, have you looked at rob's or jeff's cross tab sproc?

Yea I wonder if you could run "Execute crosstab" dynamically ?

Rock
This works exactly as I asked !!!!!!

One last thing.... Is there a easy way to replace nulls accross the entire table with a zero?

Jim
Users <> Logic

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-03 : 10:32:03

>> Yea I wonder if you could run "Execute crosstab" dynamically ?
I was thinking the same thing. dynamic dynamic sql

You can do that, the debugging gets messy though...
Jeff's crosstab is very impressive!

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-03 : 10:36:12

>> One last thing.... Is there a easy way to replace nulls accross the entire table with a zero?

In the code change this line:
SET @ddl_table = 'ALTER TABLE CycleCount ADD [' + @new_col + '] INT NULL'
To this:
SET @ddl_table = 'ALTER TABLE CycleCount ADD [' + @new_col + '] INT NOT NULL DEFAULT(0)'

That it will handle the NULL issue in the example.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources