June 11, 2014 at 9:14 am
Hi, I am trying to think of the best way to do this...Based on how I am receiving the data and how I need it transformed. From the test data below, I need to reformat it to look like the end table, hope this makes sense ...
IF OBJECT_ID('TempDB..#fruitSales','U') IS NOT NULL
DROP TABLE #fruitSales
CREATE TABLE #fruitSales
(
SalesDate DATETIME,
AppleSales INT,
OrangeSales INT
)
insert into #fruitSales
(SalesDate, AppleSales, OrangeSales)
select getdate()-5, 4, 7 union all
select getdate()-4, 6, 2 union all
select getdate()-3, 9, 1 union all
select getdate()-2, 7, 9 union all
select getdate()-1, 3, 6
SalesDate, FruitSales, SalesValue
2014-06-06 apples 5
2014-06-06 oranges 4
2014-06-07 apples 6
2014-06-07 oranges 2
2014-06-08 apples 9
2014-06-08 oranges 1
2014-06-09 apples 7
2014-06-09 oranges 9
2014-06-10 apples 3
2014-06-10 oranges 6
June 11, 2014 at 9:21 am
Your sample output doesn't seem to match your sample data but I think you want something like this.
select SalesDate, 'apples' as FruitSales, AppleSales
from #fruitSales
union all
select SalesDate, 'oranges', OrangeSales
from #fruitSales
order by SalesDate, FruitSales
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2014 at 10:02 am
This is a classic case for UNPIVOT. If you run these queries with Actual Plan specified, you'll see that it is most efficient plan. Either method has the disadvantage that all the categories of fruitsales must be known in advance so that you can designate your buckets in the UNPIVOT statement.
IF OBJECT_ID('TempDB..#fruitSales','U') IS NOT NULL
DROP TABLE #fruitSales
CREATE TABLE #fruitSales
(
SalesDate DATETIME,
AppleSales INT,
OrangeSales INT
)
insert into #fruitSales
(SalesDate, AppleSales, OrangeSales)
select getdate()-5, 4, 7 union all
select getdate()-4, 6, 2 union all
select getdate()-3, 9, 1 union all
select getdate()-2, 7, 9 union all
select getdate()-1, 3, 6
select * from #fruitsales
select SalesDate, 'apples' as FruitSales, AppleSales
from #fruitSales
union all
select SalesDate, 'oranges', OrangeSales
from #fruitSales
order by SalesDate, FruitSales
SELECT SalesDate, FruitSalesType, FruitSales
FROM
(SELECT SalesDate, AppleSales, OrangeSales
FROM #fruitsales) p
UNPIVOT
(FruitSales FOR FruitSalesType IN
(AppleSales, OrangeSales)
)AS unpvt;
GO
June 11, 2014 at 10:19 am
Agreed. UNPIVOT would be a better choice here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply