SQL Transpose the data

Question

SQL Transpose the data

tocy1980

Ten Centuries

Points: 1000
More actions
October 26, 2016 at 6:21 am

#331239

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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63434 More actions · Answer 1

Welcome to SSC.

You should read Jeff Moden's articles on Crosstabs, and how to post a question[/url]. Especially the latter, since you're new here.

Then there's Jeff's articles on Crosstabs/Pivots

First article[/url]

Second article[/url]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

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)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tocy1980 Ten Centuries Points: 1000 More actions · Answer 3

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.

tocy1980 Ten Centuries Points: 1000 More actions · Answer 4

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?

tocy1980 Ten Centuries Points: 1000 More actions · Answer 5

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?

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

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)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tocy1980 Ten Centuries Points: 1000 More actions · Answer 7

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!

tocy1980 Ten Centuries Points: 1000 More actions · Answer 8

Should I mark this topic as closed or finished and how?