Hi,



I have situation when I am trying to convert rows into columns(Dynamically). Here is the scenario -



1. I have a function(userdefined) which takes in a SQL query as input and returns the result as a table type.

2. The result is in the following fashion -

a. Say I pass "Select * from emp" as the input

b. Result would be


Column Name Column Value
Emp No 1001
Emp Name Jack
Dept No 20
City New York
Emp No 1002
Emp Name Mark
Dept No 30
City Chicago




3. What I am trying to achieve is that I need to convert the rows into columns. Something like below -



Emp No Emp Name DeptNo City

1001 Jack 20 New york

1002 Mark 30 Chicago



4. I tried using the pivot function, but that requires to pass aggregate function and provide values for those aggregate - which is not required in my case.

5. I also cannot hard-code the values as the example above is only for emp table. In the production scenario any table can be passed to the select clause and based on number of columns and rows it should generate the table



Any idea how this can be achieved ?



Thanks!