Link to home
Start Free TrialLog in
Avatar of shashankdbargaje
shashankdbargaje

asked on

I want coulmn name and value as output

Hello,

I require the output to be the name of the column and its value. I need it to be generic so it applies to all tables. The first column of output should have the name of the column and second column the value for a single row depending on where clause.
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Please give sample data before and after. This will also help testing the solution.
Avatar of ste5an
Just code it:

SELECT 'columnName', column FROM table

Open in new window


Anything else requires that you tell us more about your actual context.
Avatar of shashankdbargaje
shashankdbargaje

ASKER

EMPID      2
DEPARTMENT      fdf
ENAME      dd
SALARY      33

In this the table has columns EMPID, DEPARTMENT, ENAME, SALARY.
I want the first column of the output to have all column names in the table and the second column in output should have the value based on where clause like where EMPID =2
SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
you may look to system table: syscolumns in case you need such info.

for example:
select a.* 
from syscolumns a
where a.id = object_id('yourtablename') 

Open in new window


you may want to further explore on this.
Hello Paul,

Sorry for the confused scenario described by me. The sample data that you showed the output I want is

EMPID      1
DEPARTMENT      fdf
ENAME      aa
SALARY      22

if the where clause has EMPID = 1
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello Paul,

Thank you for the output. I tried this approach and it works but how is it possible to make it generic so that I don't have to mention the name of the columns in the query so that it can be used on any table.
Please let me know how you will make the where clause generic?

i.e. for this example you needed "where empid = 1"

for another table it might be "where customerid = 12345677"

How do you expect to handle that?

Please also identify how you are "consuming" this transformation. Does it end up in HTML?
Hello,

I want to replicate it in DB2, is it possible? The where clause is not required to be generic but the column names mentioned can they be generic in the values box.
..."I want to replicate it in DB2"
I really don't understand

how/why would I generate generic code using sql server for use on DB2? (different syntax!)

and the where clause cannot be ignored, please reconsider that, how an I arrive at generic code if the where clause isn't also generic? Please provide some examples using different tables perhaps.
I wanted to know if the cross apply concept applies for DB2 also. About the generic part I mean is that

I have mentioned the points in the query after //

select
ca.name, ca.value // what is ca
from YourTable T
cross apply (
  values
         ('EMPID'      ,cast(EMPID      as NVARCHAR(max))) // can i not mention these column names and reuse this as a procedure to be used on any table
       , ('DEPARTMENT' ,cast(DEPARTMENT as NVARCHAR(max))) // same here
       , ('ENAME'      ,cast(ENAME      as NVARCHAR(max))) // same here
       , ('SALARY'     ,cast(SALARY     as NVARCHAR(max))) // same here
  ) ca (name,value)
where T.empid = 1 // the id i will be receiving from selection so that is fine
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello,

Is there any way to do it in SQL rather than specific SQL Server
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial