January 26, 2010 at 8:08 am
I am extracting data for a particular month from Table A; I need to store this information in Table B as 12 seperate rows, 1 for each month. What is the best way to achieve this? There is more than one row to extract from Table A i.e. the SELECT FROM Table A will extract hundread of rows.
January 26, 2010 at 8:13 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2010 at 8:24 am
Ok, here is a definition of the destination table:
DECLARE @Temp TABLE
(
version_id int,
customer_id char(8),
product_id char(20),
month_id char(2),
fiscal_year char(2),
units numeric(18, 4),
value numeric(18, 4)
)
The data that will be inserted into this table comes from a seperate database. The data is the actual sales data by month and year. I want to insert the average units and average value of a sale to a customer for a product 12 times into the destination table. In other words, if my customer bought one product (Product A) in Jan for 100, I want to insert this data into @Temp for each of the twelve months.
I got as far as the following:
INSERT INTO
SELECT 1 AS version_id, customer_id, product_id, ????, ????,
AVG(actual_units), AVG(value)
January 26, 2010 at 9:32 am
To even begin to get you a working solution, I'd also need to see the definition of the source table and some sample data in it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2010 at 9:49 am
Hi Gail,
Yes I appreciate what you are saying, I will try and add more info to the post later when I've got a bit more time. 🙂
Thanks
David
January 27, 2010 at 6:49 am
DECLARE @Temp TABLE(version_id int,customer_id char(8),product_id char(20),month_id char(2),fiscal_year char(2),units numeric(18, 4),value numeric(18, 4))
INSERT INTO @Temp
SELECT
1,'1','10','1','00',100,980.66
UNION ALL
SELECT
15,'1','10','12','00',100,980.66
UNION ALL
SELECT
14,'1','10','10','00',100,980.66
UNION ALL
SELECT
13,'1','10','11','00',100,980.66
UNION ALL
SELECT
1,'1','10','1','00',100,980.66
UNION ALL
SELECT
2,'1','10','2','00',100,980.66
UNION ALL
SELECT
3,'1','10','3','00',100,980.66
UNION ALL
SELECT
4,'1','10','4','00',100,980.66
UNION ALL
SELECT
5,'1','10','5','00',100,980.66
UNION ALL
SELECT
6,'1','10','6','00',100,980.66
UNION ALL
SELECT
7,'1','10','7','00',100,980.66
UNION ALL
SELECT
8,'1','10','8','00',100,980.66
UNION ALL
SELECT
9,'1','10','9','00',100,980.66
UNION ALL
SELECT
10,'1','10','10','00',100,980.66
UNION ALL
SELECT
11,'1','10','11','00',100,980.66
UNION ALL
SELECT
12,'1','10','12','00',100,980.66
SELECT
[JAN],[FEB],[MAR],[APR],[MAY],[JUN],[JUL],[AUG],[SEP],[OCT],[NOV],[DEC]
FROM
(
SELECT
LEFT(DATENAME(MM, month_id+'/01/1900'),3) AS MONTHNAME,
units
FROM @Temp
)A
PIVOT
(
SUM(units) FOR MONTHNAME IN ([JAN],[FEB],[MAR],[APR],[MAY],[JUN],[JUL],[AUG],[SEP],[OCT],[NOV],[DEC])
)PIVOTS
Regards,
Mitesh OSwal
+918698619998
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply