SQLTeam.com | Weblogs | Forums

Unpivot using MSQuery Excel

I need to query the excel sheet as I have constraint of not using excel directly in a application.

Now I need to select only 20 out of 40 columns and pivot approx t0 columns, Problem I am running into is I am unable to Unpivot the excel using query (ms query).

Struggling searching the net for suitable solution but till now unable to achieve Unpivot functionality.

Please experts in this forum if can help with MsQuery code to write a query to unpivot selected columns using Excel datasource.

I managed to select the required column but not sure how to unpivot.

Select * from(
select F2, F3, F4, F5,
F8, F9
from
[abc$])

Here I need to unpivot F8 and F9 columns and create a result set as

F2 F3 F4 F5 Names Values

Please help, struk for more than a week.

thanks
Siva

Select * from(
 select F2, F3, F4, F5, F8, F9 from [abc$]
 )main
 unpivot ([Names] for [Values] in ([F8],[F9])) upvt
1 Like

Thanks for the answer but I am getting error message

Could not add the table[

Is their anything missing, please help

IF OBJECT_ID('TEMPDB..#table') IS NOT NULL DROP TABLE #table

create table #table (
							 F2 varchar(255)
							,F3 varchar(255)
							,F4 varchar(255)
							,F5 varchar(255)
							,F8 varchar(255)
							,F9 varchar(255) 
)

insert into #table values
('Test','Test','Test','Test','Check','Working'),
('Test','Test','Test','Test','Data','Elma'),
('Test','Test','Test','Test','Anything','Fox')


Select * from(
 select F2, F3, F4, F5, F8, F9 from #table
 )main
 unpivot ([Values] for [Names] in ([F8],[F9])) upvt

seems to be working for me

Thanks for reply..

Will this syntax work to query sql using MS Query, because I am querying MS Query and unable to get the pivot with this syntax.

Thanks for continous followup