Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Select from Table column and insert into row

Posted on 2014-02-14
5
Medium Priority
?
442 Views
Last Modified: 2014-02-21
Hi all

I have sql MainTable with 12 Columns and 100 Rows A to L
Primary Key A
Foreign Key B

I need to extract columns C - L and Insert them in a new RowTable.
The RowTable has the following schema
Primary Key ID = Identifier
Foreign Key fkey_A reference Primary Key A MainTable
Foreign Key fkey_B reference Foreign Key A MainTable
ColumnObject = varChar

ColumnObject will contain each values of C - L column from MainTable.

I am looking for a store-procedure that could help me achive this objective.
0
Comment
Question by:ZURINET
  • 3
5 Comments
 

Author Comment

by:ZURINET
ID: 39858773
The code below is close to what i need but I need a way to select only the needed column

DECLARE @YourTableName   varchar(1000)
DECLARE @YourTableWhere  varchar(1000)
DECLARE @YourQuery       varchar(max)
SET @YourTableName='YourTestTable'
set @YourTableWhere='y.RowID=1'


SELECT
    @YourQuery=ISNULL(@YourQuery+' UNION ','')
        + 'SELECT '''+COLUMN_NAME+''', CONVERT(varchar(max),'+COLUMN_NAME+') FROM '+@YourTableName+' y'+ISNULL('  WHERE '+@YourTableWhere,'')
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = @YourTableName

Open in new window

0
 

Author Comment

by:ZURINET
ID: 39858856
SELECT
    @YourQuery=ISNULL(@YourQuery+' UNION ','')
        + 'SELECT '''+COLUMN_NAME+''', CONVERT(varchar(max),'+COLUMN_NAME+') FROM '+@YourTableName+' y'+ISNULL('  WHERE '+@YourTableWhere,'')
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = @YourTableName 
  AND COLUMN_NAME IN ('Hotel, Payment')

Open in new window


The last thing I need to do is to select only those column without null values..
0
 
LVL 35

Expert Comment

by:YZlat
ID: 39859147
to exclude null values use

COLUMN_NAME NOT IS NULL

in your where clause
0
 

Author Comment

by:ZURINET
ID: 39859411
That will not work because.. there is not column name that is null

I need to exclude the content and not the name..

I found a good lead

http://www.codeproject.com/Articles/489617/CreateplusaplusCursorplususingplusDynamicplusSQLpl
0
 
LVL 41

Accepted Solution

by:
Sharath earned 1000 total points
ID: 39860745
try like this.
  
SELECT
    @YourQuery=ISNULL(@YourQuery+' UNION ','')
        + 'SELECT '''+COLUMN_NAME+''', CONVERT(varchar(max),'+COLUMN_NAME+') FROM '+@YourTableName+' y WHERE '+COLUMN_NAME+' IS NOT NULL'+ISNULL('  AND '+@YourTableWhere,'')
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = @YourTableName 
  AND COLUMN_NAME IN ('Hotel, Payment') 

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question