Solved

Select from Table column and insert into row

Posted on 2014-02-14
5
401 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 40

Accepted Solution

by:
Sharath earned 500 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now