October 26, 2016 at 6:21 am
Hello guys,
I am the newbie in this field so I really appreciate your help.
As you can see from the attached file, my data currently looks like in the tab "Original data"
And now I need some query to transpose and put the data in new format like it's in the sheet "Final".
Don't pay attention on that I have attached the excel file as I have linked this excel file to SQL studio management.
Thank you in advance.
October 26, 2016 at 8:13 am
October 26, 2016 at 8:24 am
Actually, it's not precisely a cross tabs issue, but some kind of unpivoting.
Here's an example which is partially explained in this article: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
SELECT [Customer], [Section], [Data], [Year], Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
FROM Table1
CROSS APPLY( VALUES (2014, [Jan-14], [Feb-14], [Mar-14], [Apr-14], [May-14], [Jun-14], [Jul-14], [Aug-14], [Sep-14], [Oct-14], [Nov-14], [Dec-14]),
--(2015, [Jan-15], [Feb-15], [Mar-15], [Apr-15], [May-15], [Jun-15], [Jul-15], [Aug-15], [Sep-15], [Oct-15], [Nov-15], [Dec-15])
(2015, [Jan-15], [Feb-15], [Mar-15], [Apr-15], [May-15], [Jun-15], [Jul-15], [Aug-15], 0, 0, 0, 0)) u([Year], Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
October 26, 2016 at 8:32 am
Luis Cazares (10/26/2016)
Actually, it's not precisely a cross tabs issue, but some kind of unpivoting.Here's an example which is partially explained in this article: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
SELECT [Customer], [Section], [Data], [Year], Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
FROM Table1
CROSS APPLY( VALUES (2014, [Jan-14], [Feb-14], [Mar-14], [Apr-14], [May-14], [Jun-14], [Jul-14], [Aug-14], [Sep-14], [Oct-14], [Nov-14], [Dec-14]),
--(2015, [Jan-15], [Feb-15], [Mar-15], [Apr-15], [May-15], [Jun-15], [Jul-15], [Aug-15], [Sep-15], [Oct-15], [Nov-15], [Dec-15])
(2015, [Jan-15], [Feb-15], [Mar-15], [Apr-15], [May-15], [Jun-15], [Jul-15], [Aug-15], 0, 0, 0, 0)) u([Year], Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
Thanks, I'm going to check this and back here with feedback.
October 26, 2016 at 9:05 am
I am still checking the data but it seems that query works excellent.
One more question.
I see few zeroes after [Aug-15] and I suppose it is because I don't have any data for rest months. Am I right?
Actually, I have reduced the size of data for this example and in reality I have data up to 2017 so I need to put all those months there instead zero?
October 26, 2016 at 9:29 am
This works like a charm!
Thank you very much!
And one more question 🙂
If I need to put the data in the form like it is in the picture how to do this?
October 26, 2016 at 9:44 am
That's right, you need to replace the zeros with the columns available.
To obtain the data in the new way (which should be the correct way to store it) you need to reduce the columns and increase the rows. Here's a short example:
SELECT [Customer], [Section], [Data], Date, Qty
FROM Table1
CROSS APPLY( VALUES (CAST( '20140101' AS date), [Jan-14]),
(CAST( '20140201' AS date), [Feb-14])) u(Date, Qty)
October 26, 2016 at 11:21 am
Luis Cazares (10/26/2016)
That's right, you need to replace the zeros with the columns available.To obtain the data in the new way (which should be the correct way to store it) you need to reduce the columns and increase the rows. Here's a short example:
SELECT [Customer], [Section], [Data], Date, Qty
FROM Table1
CROSS APPLY( VALUES (CAST( '20140101' AS date), [Jan-14]),
(CAST( '20140201' AS date), [Feb-14])) u(Date, Qty)
Thank you a million!
October 26, 2016 at 11:26 am
Should I mark this topic as closed or finished and how?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply