update a column of a table by selectiong value from another table whose column name is the row name of fist table

Question

0.00/5 (No votes)

See more:

I have one table #table with columns columnname,value

having contents say...

SQL

Columnname      value

XYZ              NUll
ABC              null

another table I have say #datatable with Columns XYZ, ABC, LMN and having data

SQL

XYZ      ABC         LMN

 1       2           3

I need to update first #table the result of the sql query as,

SQL

ColumnName      Value
XYZ             1
ABC             2

Please help

here is what I tried

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
					 
select @cols = STUFF((SELECT ',' + QUOTENAME(columnname) 
                    from #table
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
print @cols
set @query = N'SELECT ' + @cols + N' from 
             #datatable
               unpivot 
            (
            value    
        for columnname in (' + @cols + N')
            ) p '

but error

Msg 207, Level 16, State 1, Line 1
Invalid column name 'XYZ'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ABC'.

Posted 30-Oct-14 1:06am

Member 10112611

Updated 30-Oct-14 1:48am

v3

Add a Solution

Comments

King Fisher 30-Oct-14 7:33am

How did u get the Value of XYZ,ABC ..

Member 10112611 30-Oct-14 7:34am

you mean in second table? this is just a test entry I made to express my requirement

King Fisher 30-Oct-14 7:55am

actually What do u want to Do? Explain From the Beginning of your Task .its not Clear

3 solutions

Solution 4

Thank you for the helps from all to reach at unpivot concept

Posted 30-Oct-14 2:31am

Member 10112611

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shweta N Mishra · Accepted Answer · 2014-10-30T01:11:00

Solution 1

You can Un-Pivot the #datatable table and then make a simple updated as you will have
below values and format after unpivot

XYZ 1
ABC 2
LMN 3

Posted 30-Oct-14 1:11am

Shweta N Mishra

Comments

Member 10112611 30-Oct-14 7:13am

I tried one query , but error, can u help me on Solving
this?

King Fisher 30-Oct-14 7:27am

What have you tried so far?

Member 10112611 30-Oct-14 7:48am

I modified and added the query I tried and the error message also

Member 10112611 30-Oct-14 7:27am

Columns or rows of both table are not predefined is one of the constraint..there can be n number of rows for the first table and n numebr of columns for second one

Member 10112611 30-Oct-14 7:58am

Hi shweta ,

I tried the above query to unpivot, but error. can u help?

Shweta N Mishra 30-Oct-14 9:04am

Apology , i guess you got the Unpivot solution. As you said your column names are dynamic you may fetch the column names from sys.syscolumn only if the table is physical table as given below.

select @cols=Coalesce(@cols,'')+name+','
from Sys.syscolumns where ID=Object_ID('tablename')

Also it would be great for other readers too if you can post your Successful query for future reference.

Member 10112611 30-Oct-14 9:10am

Not exactly, my table having different data types and unpivot is not working proprly. I am trying with cursor now

Shweta N Mishra 30-Oct-14 9:26am

different datatype ?, Then how would you updated the target table(#table).

Member 10112611 30-Oct-14 9:33am

#table is for reporting purpose, So i made all datatype as varchar and updated through cursor

Member 10112611 · Accepted Answer · 2014-10-30T02:19:00

Solution 3

SQL

SELECT  COLUMNANME,VALUE  from
             #datatable
               unpivot
            (
            value
        for COLUMNANME in (XYZ,ABC)
            ) p

Posted 30-Oct-14 2:19am

Member 10112611