Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert years Expand / Collapse
Author
Message
Posted Thursday, October 18, 2012 3:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 7:21 PM
Points: 3, Visits: 11
Hi,
Can Anyone help me with this, please

I have a Table like this

Es Year January February March
E0003 2008 XXXXX XXXXX XXXXX
E0003 2009 XXXXY XXXXY XXXXY
E0004 2008 XXXYY XXXYY XXXYY
E0004 2009 ZZYYY ZZYYY ZZYYY

And I need to convert that table into this:

Es Jan_08 Jan_09 Feb_08 Feb_09 Mar_08 Mar_09
E0003 XXXXX XXXXY XXXXX XXXXY XXXXX XXXXY
E0004 XXXYY ZZYYY XXXYY ZZYYY XXXYY ZZYYY


Post #1374598
Posted Thursday, October 18, 2012 3:52 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:03 PM
Points: 1,480, Visits: 1,028
What have you tried, and where are you getting stuck?

Please read this
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Look up Case statement, and
group by Es
Or the Pivot Functions
Post #1374599
Posted Thursday, October 18, 2012 4:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:19 PM
Points: 36,995, Visits: 31,523
hugo_17_23 19942 (10/18/2012)
Hi,
Can Anyone help me with this, please

I have a Table like this

Es Year January February March
E0003 2008 XXXXX XXXXX XXXXX
E0003 2009 XXXXY XXXXY XXXXY
E0004 2008 XXXYY XXXYY XXXYY
E0004 2009 ZZYYY ZZYYY ZZYYY

And I need to convert that table into this:

Es Jan_08 Jan_09 Feb_08 Feb_09 Mar_08 Mar_09
E0003 XXXXX XXXXY XXXXX XXXXY XXXXX XXXXY
E0004 XXXYY ZZYYY XXXYY ZZYYY XXXYY ZZYYY




You will need to "unpivot" or "uncrosstab" the data so that it's in a vertical format and then "repivot" or "recrosstab" to the desired layout.

For coded help, please see the first link in my signature line below.

As a sidebar, the original data is stored as if it were a spreadsheet, which is a denormalized form and causes a lot of the problems like the very problem you're currently having. My recommendation is that once you have "unpivoted" the data, you keep it that way.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1374604
Posted Thursday, October 18, 2012 4:30 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:15 PM
Points: 3,564, Visits: 7,702
I'm sure this article will help you.
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1374606
Posted Thursday, October 18, 2012 4:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:15 PM
Points: 3,564, Visits: 7,702
Jeff Moden (10/18/2012)

You will need to "unpivot" or "uncrosstab" the data so that it's in a vertical format and then "repivot" or "recrosstab" to the desired layout.

For coded help, please see the first link in my signature line below.

As a sidebar, the original data is stored as if it were a spreadsheet, which is a denormalized form and causes a lot of the problems like the very problem you're currently having. My recommendation is that once you have "unpivoted" the data, you keep it that way.


For this query, I'm sure he doesn't need to "unpivot" the data.
However, I support your recommendation on keep the data normalized.



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1374609
Posted Saturday, October 27, 2012 11:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:19 PM
Points: 36,995, Visits: 31,523
Luis Cazares (10/18/2012)
Jeff Moden (10/18/2012)

You will need to "unpivot" or "uncrosstab" the data so that it's in a vertical format and then "repivot" or "recrosstab" to the desired layout.

For coded help, please see the first link in my signature line below.

As a sidebar, the original data is stored as if it were a spreadsheet, which is a denormalized form and causes a lot of the problems like the very problem you're currently having. My recommendation is that once you have "unpivoted" the data, you keep it that way.


For this query, I'm sure he doesn't need to "unpivot" the data.
However, I support your recommendation on keep the data normalized.

For hard coded dates, I agree. But how often are hard coded dates going to be safe on the 1st of the next year?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1377991
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse