November 10, 2008 at 9:59 am
I call store procedure in the store procedure get different colums every time I run the code
Here is my statment:
EXECUTE sp_crosstab 'SELECT shipdate FROM table1
GROUP BY shipdate', 'sum(cost)','service','Table1 '
How can I create a temp table with my select statment. I can't hard code the fields because like I said get different colums every time I run the code.
Thank you
November 10, 2008 at 10:07 am
Are you doing a pivot in your proc call?
What is it that you want to achieve here it might help us help you.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 10, 2008 at 10:11 am
Yes, I am creating a povot. I need the data that I get from select statment, create a table with fields.
Servicedata UPS1 UPS2 UPS3 UPS4
1/1/2008 5 7 8 5
I need this table to be created, but I can't hard code the fields because they are different.
Thank you
November 10, 2008 at 10:15 am
Ok cool.
My question is why do you need a table, I'm going to assume that you want to work with the data set from the proc.
I assume this because otherwise your DB will full up with random tables each time you run the proc.
So my question is this can you just work with the data in the proc?
or should I say what is it you want to do with the data once it's in the table. Cause if at runtime you don't know what columns come back then how can you work on the table?
If however I'm wrong you simply going to display the results from the table then I wonder why we need the table at all?
Please let me know what your code does after the table is created correctly 🙂
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 10, 2008 at 10:54 am
I need a data in the table because, I need to create a report in Access database. Thank you
November 10, 2008 at 10:58 am
Can I create a view with calling store procedure?
November 11, 2008 at 2:06 am
You shouldn't need a table to create a report.
If you open up an ADO recordset which is created from your Procedure then you don't need to know the columns at all, you can just use the recordset Field property to index which ever columns you want.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 11, 2008 at 2:34 pm
If you really need to, create a table variable then SELECT that INTO #temp
i.e.
DECLARE @Table TABLE
(ID INT,
Field1 VARCHAR(20) etc)
then
INSERT INTO @Table (ID, Field1 and so on
EXECUTE sp
SELECT * INTO #Temp FROM @Table
a big kludge but hey, it's what you asked for
November 11, 2008 at 5:54 pm
Thank you so much, but what ither solution do you have? The report will be created in access db and I thought populating data to the table from pivot would be a good idea.Thank you again
November 11, 2008 at 6:24 pm
You can create a dynamic pivot to do what you want.
This article contains a stored procedure which creates a dynamic pivot for you.
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Or, if you post some sample DDL and data we can help you out.
This article explains how to post a question in order to get the best help.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 12, 2008 at 6:27 am
My data sample
Ship Date Service (Sum ofPackages) Sum of Cost
10/28/2008 12:00:00 AMUPS 3 Day 11 65.7100
10/28/2008 12:00:00 AMUPS Next Day Air21 145.0400
10/28/2008 12:00:00 AMUPS Ground426 1898.8700
10/28/2008 12:00:00 AMUPS Basic1573 6652.7200
10/28/2008 12:00:00 AMUPS Next Day Air Saver68790.6300
10/28/2008 12:00:00 AMUPS Mail Innovations Expedited9712741.3200
I need: Results in a table:
Ship date UPS 3 Day SumofPackages UPS 3 DaySum of Cost UPS Next Day AirSumofPackages UPS Next Day Air Sum of Cost (with all of my service columns)
10/28/2008 11 65.7100 21 145.0400
Thank you
November 12, 2008 at 8:06 am
I'm really busy at work today, I'll have to look at this tonight.
In the meanwhile, check out this article and see if you can't figure it out on your own.
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
The post shows a dynamic pivot stored procedure. You don't have to use the stored procedure, but from it you can learn how to make your own dynamic pivot.
November 12, 2008 at 6:44 pm
Give this a spin. I want to make sure we're on the right track.
I know it's not perfect yet. I wasn't sure what you wanted to do with the column names.
[font="Courier New"]
-- create a sample table
CREATE TABLE #ShippingInfo
(
ShipDate DATETIME
,[Service] VARCHAR(50)
,TotalPackages INT
,TotalCost MONEY
)
-- insert some data
INSERT INTO #ShippingInfo
SELECT '10/28/2008 12:00:00 AM','UPS 3 Day',11,65.7100
UNION ALL SELECT '10/28/2008 12:00:00 AM','UPS Next Day Air',21,145.0400
UNION ALL SELECT '10/28/2008 12:00:00 AM','UPS Ground',426,1898.8700
UNION ALL SELECT '10/28/2008 12:00:00 AM','UPS Basic',1573,6652.7200
UNION ALL SELECT '10/28/2008 12:00:00 AM','UPS Next Day Air Saver',68,790.6300
UNION ALL SELECT '10/28/2008 12:00:00 AM','UPS Mail Innovations Expedited ',971,2741.3200
-- create a variable to store the column names
DECLARE @Columns VARCHAR(MAX)
-- select the distinct column names
;WITH SelectColumns AS
(
SELECT DISTINCT [Service]
FROM #ShippingInfo
)
-- concatenate the columnns into a csv string
SELECT @Columns = COALESCE(@Columns+',','') + '['+ [Service] +']'
FROM SelectColumns
-- create variable to store our dynamic pivot
DECLARE @PivotSQL NVARCHAR(MAX)
-- now create our dynamic pivot
-- create two pivots one for total packages and one for total cost
-- then join the two pivots on ship date
SET @PivotSQL =
N';WITH TotalPackageData AS
(
SELECT ShipDate
,[Service]
,TotalPackages
FROM #ShippingInfo
)
, TotalPackageAggreagate AS
(
SELECT ShipDate, '
+ @Columns +
' FROM TotalPackageData
PIVOT
(
SUM(TotalPackages)
FOR [Service] IN ( '
+ @Columns +
' )) AS Pivot1
), TotalCostData AS
(
SELECT ShipDate
,[Service]
,TotalCost
FROM #ShippingInfo
) , TotalCostAggreagate AS
(
SELECT ShipDate, '
+ @Columns +
' FROM TotalCostData
PIVOT
(
SUM(TotalCost)
FOR [Service] IN ( '
+ @Columns +
') ) AS Pivot2
)
SELECT *
FROM TotalPackageAggreagate A
JOIN TotalCostAggreagate B ON A.ShipDate = B.ShipDate '
-- execute pivot
EXEC sp_ExecuteSQL @PivotSQL
-- clean up
DROP TABLE #ShippingInfo
[/font]
November 13, 2008 at 7:10 am
dynamic_pivot
'select shipdate,TotalPackages from #ShippingInfo',
'service',
'min(TotalPackages)'
Use the procedure from http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Failing to plan is Planning to fail
November 13, 2008 at 7:16 am
That's not going to work.
He needs two pivots.
One for TotalPackages and one for TotalCost.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply