I want coulmn name and value as output


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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Please give sample data before and after. This will also help testing the solution.
ste5anSenior DeveloperCommented:
Just code it:

SELECT 'columnName', column FROM table

Open in new window

Anything else requires that you tell us more about your actual context.
shashankdbargajeAuthor Commented:
EMPID      2
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

PortletPaulEE Topic AdvisorCommented:
Hi. Welcome to experts-exchange.

Please don't expect experts, who are volunteers, to simply code up everything you ask for. There is subtle but important difference between a valid question and a request for a free solution.

Now, also there is a method for asking SQL related questions that helps you get your solution quickly and accurately.
Provide these 2 things.
A: Sample data and
B: Expected result.
both are required.

Now whilst you have supplied an expected result it does not cater for all conditions.

Are you assuming you ONLY EVER rotate a SINGLE ROW of source data into those 2 columns?

e.g. What is the expected result if this is the sample data?

1     fdf        aa       22
2     fdf        dd       33

Open in new window

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
shashankdbargajeAuthor Commented:
Hello Paul,

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

EMPID      1
ENAME      aa
SALARY      22

if the where clause has EMPID = 1
PortletPaulEE Topic AdvisorCommented:
Thank you, so the requirement is for a single row of source data. However this type of data transformation (or "rotation") is NOT a great fit for SQL. The reason for this is that in SQL a column needs to be a single "data type" (although it is possible to use "SQL_Variant" but this is limited in length).

If you are using PHP/HTML then I would advise you to do it there, not the sql.

Here however is an approach to the transform, using CROSS APPLY & VALUES. I like this approach as it is both efficient and I find the syntax easy to understand as I layout the rows visually as rows within the VALUES.
ca.name, ca.value
from YourTable T
cross apply (
         ('EMPID'      ,cast(EMPID      as NVARCHAR(max)))
       , ('DEPARTMENT' ,cast(DEPARTMENT as NVARCHAR(max)))
       , ('ENAME'      ,cast(ENAME      as NVARCHAR(max)))
       , ('SALARY'     ,cast(SALARY     as NVARCHAR(max)))
  ) ca (name,value)
where T.empid = 1

Open in new window

Note I have used NVARCHAR(max) as the data type for the value column, but even this fails for timestamp and image columns.
see: https://msdn.microsoft.com/en-us/library/ms187928.aspx

As mentioned you can use the system tables/views to get column names.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shashankdbargajeAuthor Commented:
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.
PortletPaulEE Topic AdvisorCommented:
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?
shashankdbargajeAuthor Commented:

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.
PortletPaulEE Topic AdvisorCommented:
..."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.
shashankdbargajeAuthor Commented:
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 //

ca.name, ca.value // what is ca
from YourTable T
cross apply (
         ('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
PortletPaulEE Topic AdvisorCommented:
CROSS APPLY is quite specific to SQL Server and the technique I have used wouldn't apply in any other dbms. Sorry, I did not realize this was the sense of "generic" I thought you want it to apply to any SQL Server table, but you want it to apply to any table and any dbms platform is that correct?
I don't think you can write such a "generic SQL"

The "ca" is an "alias" given to the cross apply.

for the where clause, it isn't the number that concerns me, it is the field name(s)

If you need the sql code to apply to more than on table then you cannot always use EMPID as the field name.
shashankdbargajeAuthor Commented:

Is there any way to do it in SQL rather than specific SQL Server
About the only way it can be done across multiple DBMSs is to generate dynamic SQL statements. And doing that implies either some pretty sophisticated stored procs or significant coding in some HLL. It's possible to do it somewhat similarly in just about any decent DBMS, but there will be some differences between them.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.