May 23, 2013 at 10:55 am
I have data that looks like the following:
Sales_2012Planfiscal_week_2013Sales_2013LYPN
232.2935700286.86422013-01-05263.14484000.132811553931518638-0.0826852665251378
237.2016700286.86422013-01-12258.23109000.088656289814485707-0.0998144463016142
232.0180700286.86422013-01-19264.82219000.141386056698083903-0.0768380765586006
238.2087000286.86422013-01-26266.36604000.118204498828128443-0.0714562634427699
253.8050500297.07142013-02-02267.93892000.055687899039045913-0.0980658655463566
But I need the results to look like:
Sales1/51/121/191/262/22/92/162/23
2012213216225241246247241265
Plan230233243260266267260286
2013217222226243250251261271
% to LY101.9%102.8%100.4%100.8%101.6%101.6%108.3%102.3%
% to PN94.3%95.2%93.0%93.4%94.1%94.1%100.3%94.7%
How can I get my data to be look like this. I need a combination of UnPivot and Pivot - but my dates vary and I don't want an IN statement with hardcoded dates. I need dynamic but not sure how to build this. Please help.
May 23, 2013 at 11:03 am
DDL of table and sample data in a format of INSERT statements would really help.
Please read this one: http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you follow the above, the relevant help will arrive much sooner than you could expect.
May 23, 2013 at 11:57 am
I am sorry...
I am using a stored procedure built by another developer to provide data in above post.
IF @Department in ('1,5') --Grocery
BEGIN
SELECT (m2.Sales_2012/1000) as Sales_2012
,(m4.Grocery/1000) as 'Plan'
,m3.fiscal_week_2013
,(m3.Sales_2013/1000) as Sales_2013
,(m3.Sales_2013/m2.Sales_2012)-1 as LY
,(m3.Sales_2013/m4.Grocery)-1 as PN
FROM @Main_2012 m2
inner join @Main_2013 m3 on m2.ID2012=m3.ID2013
inner join [IT-Reporting].dbo.RptMerchandising m4 on m4.id = m2.ID2012
I need to report on this data but I need to unpivot data fields and pivot the fiscal week field.
May 23, 2013 at 12:54 pm
As Eugene said, we need some information to be able to help. We can't see your screen and we are not familiar with your project. We need to have something to work with in order to help.
_______________________________________________________________
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/
May 30, 2013 at 7:20 am
Hi,
Hope this works for you:
---Creating Table
Create table Ex
(
Sales_2012Float,
Plann Float,
fiscal_week_2013 Date,
Sales_2013 Float,
LY Float,
PN Float
)
--Inserting Data
Insert Into Ex
Select 232.2935700,286.8642,'2013-01-05',263.1448400,0.132811553931518638,-0.0826852665251378
Union ALL
Select 237.2016700,286.8642,'2013-01-12',258.2310900,0.088656289814485707,-0.0998144463016142
Union ALL
Select 232.0180700,286.8642,'2013-01-19',264.8221900,0.141386056698083903,-0.0768380765586006
Union ALL
Select 238.2087000,286.8642,'2013-01-26',266.3660400,0.118204498828128443,-0.0714562634427699
Union ALL
Select 253.8050500,297.0714,'2013-02-02',267.9389200,0.055687899039045913,-0.0980658655463566
--Dynamic Query For your requirement
Declare @sql Varchar(MAX)
Select @sql = 'Select '
Select @sql = @sql + '''2012'' As Sales, ' + STUFF((Select ',MAX(Case When fiscal_week_2013 = ''' + Cast(fiscal_week_2013 As Varchar) + ''' Then Sales_2012 Else '''' End) As [' + Cast(fiscal_week_2013 As Varchar) + ']'
From (Select *, ROW_NUMBER() Over(Order By Cast(fiscal_week_2013 As Varchar)) As rn From Ex ) AS X FOR XML PATH('')),1,1,'') + ' fROM eX'
Select @sql = @sql + ' Union ALL Select ''Plannn'', ' + STUFF((Select ',MAX(Case When fiscal_week_2013 = ''' + Cast(fiscal_week_2013 As Varchar) + ''' Then Plann Else '''' End) As [' + Cast(fiscal_week_2013 As Varchar) + ']'
From (Select *, ROW_NUMBER() Over(Order By Cast(fiscal_week_2013 As Varchar)) As rn From Ex ) AS X FOR XML PATH('')),1,1,'') + ' From Ex'
Select @sql = @sql + ' Union ALL Select ''2013'', ' + STUFF((Select ',MAX(Case When fiscal_week_2013 = ''' + Cast(fiscal_week_2013 As Varchar) + ''' Then Sales_2013 Else '''' End) As [' + Cast(fiscal_week_2013 As Varchar) + ']'
From (Select *, ROW_NUMBER() Over(Order By Cast(fiscal_week_2013 As Varchar)) As rn From Ex ) AS X FOR XML PATH('')),1,1,'') + ' From Ex'
Select @sql = @sql + ' Union ALL Select ''LY'', ' + STUFF((Select ',MAX(Case When fiscal_week_2013 = ''' + Cast(fiscal_week_2013 As Varchar) + ''' Then LY Else '''' End) As [' + Cast(fiscal_week_2013 As Varchar) + ']'
From (Select *, ROW_NUMBER() Over(Order By Cast(fiscal_week_2013 As Varchar)) As rn From Ex ) AS X FOR XML PATH('')),1,1,'') + ' From Ex'
Select @sql = @sql + ' Union ALL Select ''PN'', ' + STUFF((Select ',MAX(Case When fiscal_week_2013 = ''' + Cast(fiscal_week_2013 As Varchar) + ''' Then Cast(PN As Varchar) Else '''' End) As [' + Cast(fiscal_week_2013 As Varchar) + ']'
From (Select *, ROW_NUMBER() Over(Order By Cast(fiscal_week_2013 As Varchar)) As rn From Ex ) AS X FOR XML PATH('')),1,1,'') + ' From Ex'
Execute (@sql)
I didnt include the % to LY and % to PN part...........hope you can that into the above query.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply