Solved

Select from Table column and insert into row

Posted on 2014-02-14
5
421 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

622 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