Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1879
Members Online:
1
Guests Online:
100

Total Post History
Posts:
81448
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
mwojcik

Other

FAQ
All times are UTC [ DST ]

Google Ads

Dynamic unpivot

Microsoft SQL Server
Please wait...

Dynamic unpivot

Postby Venkatesh on Mon Nov 19, 2012 5:51 am

Hi, Please find screenshot of excel spreadsheet attached. How do we do dynamic unpivot.

Thanks..........
You do not have the required permissions to view the files attached to this post.
Venkatesh
Newbie
Newbie
 
Posts: 2
Joined: Mon Nov 19, 2012 5:44 am
Unrated

Re: Dynamic unpivot

Postby Chopstik on Mon Nov 19, 2012 11:46 pm

Is this a pivot table in Excel? Or is the data simply in a pivot format in Excel? And are you looking to unpivot in Excel or in SQL Server?
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

WAR IS PEACE • FREEDOM IS SLAVERY • IGNORANCE IS STRENGTH

Winston Smith
User avatar
Chopstik
LTD Admin
LTD Admin
LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346
LTD Silver - Rating: 346
 
Posts: 4316
Joined: Thu Oct 11, 2007 2:04 pm
Unrated

Re: Dynamic unpivot

Postby Venkatesh on Tue Nov 20, 2012 1:16 am

Hi, I get that data in a pivot format from a excel source and I am trying to load that data into a SQL Server table using dynamic unpivot because the data that we get could for a week or more than a week.

Thanks............
Venkatesh
Newbie
Newbie
 
Posts: 2
Joined: Mon Nov 19, 2012 5:44 am
Unrated

Re: Dynamic unpivot

Postby SQLDenis on Tue Nov 20, 2012 12:21 pm

Probably the easiest would be by way of VBA code and just doing the inserts from Excel

Otherwise dump it into a staging table in SQL and do an unpivot from there...but if the range always changes then VBA code would be the easiest IMHO A5:A7 would be the states and then B5:E7 would be the values, just loop over it

Of course it would be nice if I remembered exactly how to do this in VBA
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond