Change row to column when inserting to temp table

Question

Hi all, I need some help with this - I want to create a temporary table. To the temp table I want to insert a row from a different table BUT I want this row to be in a column form inside the temp table. This row is from a simple "select * from table" query

*table 1*

Name | ID | Address | Title | Email

AAA | 10 | NY | Mr | mr@yah

BBB | 20 | WA | Ms | ms@gm

CCC | 30 | LA | Mr | mr1@hg

I want to be able to do " INSERT INTO #tmptable (some manipulation for transpose purpose) SELECT * FROM table1 WHERE Name = 'AAA' " && the query " INSERT INTO #tmptable (some manipulation for transpose purpose) select COLUMN_NAME from INFORMATION.SCHEMA.COLUMNS where TABLE_NAME = 'table 1' "

*#tmptable*

colname1 | colname2

AAA | Name

10 | ID

NY | Address

Mr | Title

mr@yah | Email

After I have those two columns I want to do a query on the #tmptable "select colname1 from #tmptable where colname2 = 'ID' -> all I really want is to get the '10' back Is it possible to do this query? how can I define the column names in the temp table? and when should I do "DROP #tmptable"

thanks AD

Answer 1

If you have sql server 2005 or later then one way you can do this is to use UNPIVOT. Here is working example:

use tempdb

create table table1 
       (Name varchar(3)
       ,ID int
       ,Address varchar(2)
       ,Title varchar(2)
       ,Email varchar(10))

insert table1 
select 'AAA' , 10 , 'NY' , 'Mr' , 'mr@yah'
union all select 'BBB' , 20 , 'WA' , 'Ms' , 'ms@gm'
union all select 'CCC' , 30 , 'LA' , 'Mr' , 'mr1@hg'

go

select up.*
into #tmptable
from (
      select  convert(varchar(12), [id])         as [id]
              ,convert(varchar(12), [name])      as [name]
              ,convert(varchar(12), [address])   as [address]
              ,convert(varchar(12), [title])     as [title]
              ,convert(varchar(12), [email])     as [email]
       from   table1
       where  name = 'AAA'
       ) t
unpivot (colname1 for colname2 in ([name],[id],[address],[title],[email])) up

select * from #tmptable where  colname2 = 'id'

go
drop table #tmpTable
drop table table1

output:

colname1     colname2
------------ ----------
10           id

Change row to column when inserting to temp table

Follow this question

Related questions