Select from Table column and insert into row

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.
ZURINETAsked:
Who is Participating?
 
SharathData EngineerCommented:
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
 
ZURINETAuthor Commented:
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
 
ZURINETAuthor Commented:
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
 
YZlatCommented:
to exclude null values use

COLUMN_NAME NOT IS NULL

in your where clause
0
 
ZURINETAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.