Unpivot using MSQuery Excel

Other Tech Topics
1

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

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

Thanks for the answer but I am getting error message

Could not add the table[

Is their anything missing, please help

4 
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
5

seems to be working for me

6

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