Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to rotate rows into columns in a table using T-SQL

Posted on 2004-04-28
14
Medium Priority
?
15,368 Views
Last Modified: 2009-07-29
I have a table named Table1 contains records like the following,

field name          data type          length
------------          -----------         --------
field1                 nvarchar           60
field2                 nvarchar           60
field3                 int                    4
field4                 datetime           8

How to create a new table using T-SQL with fields and types get from Table1 above.

Thanks in advance
0
Comment
Question by:ihenry
[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
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 20

Author Comment

by:ihenry
ID: 10941411
If possible without using cursor, please.
0
 
LVL 3

Expert Comment

by:Frostbyte_Zero
ID: 10941454
TransactSQL does not have an option to transpose.

You could export the data to Excel and transpose each column into a row and then Import the data back. This could be done as long as you don't exceed the row or column limitations in Excel.

Other than that you must use a Cursor.
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10941500
You can do this without a cursor, but it can be messy.

What exactly do you want your end table to look like? Just to be clear.

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 20

Author Comment

by:ihenry
ID: 10941547
I want to have a table structure something like this

create table NewTable
(
      field1      nvarchar(60),
      field2      nvarchar(60),
      field3      int,
      field4      datetime
)

But is has to be dynamically, since the number of records in Table1 could increase or decrease.
0
 
LVL 4

Accepted Solution

by:
BulZeyE earned 1400 total points
ID: 10941641

try:

create table #test (fieldname varchar(100), datatype varchar(100), length varchar(50))

insert into #test
(fieldname,datatype,length)
select 'field1',                 'nvarchar',           '60'
union all
select 'field2',                 'nvarchar',           '60'
union all
select 'field3',                 'int',                    '4'
union all
select 'field4',                 'datetime',           '8'

declare @SQLStatement      nvarchar(2000),
        @ColumnDefinition nvarchar(1000),
        @TableName        nvarchar(50)

set @TableName = 'ThisIsMyTableName'

select @SQLStatement = 'CREATE TABLE ' + @TableName + ' (**COLUMNDEFINITION**)'

select @ColumnDefinition = COALESCE(@ColumnDefinition + ',','')
       + FieldName
       + ' '
       + DataType
       + CASE
           WHEN DataType in ('VARCHAR','NVARCHAR','CHAR','NCHAR','FLOAT') -- THIS CAN BE ALTERED TO SUIT YOUR NEEDS
            AND Length IS NOT NULL THEN '(' + Length + ')'
           ELSE ''
         END
  FROM #TEST

drop table #test

SET @SQLStatement = REPLACE(@SQLStatement,'**COLUMNDEFINITION**',@ColumnDefinition)

EXEC(@SQLStatement)

0
 
LVL 7

Assisted Solution

by:ChrisFretwell
ChrisFretwell earned 300 total points
ID: 10941642
Do you want to create a new table structure based on the values in the original table not populate a table with values in the original.

Thats not too hard. For this code, you will need to expand the case statement to format the data type correctly. I only used the ones in your example. But it should get you the idea. If you want to

declare @sql varchar(2000)

set @sql = 'create table newtable ('
select @sql = @sql  + char(13)+char(10)+ [field name] + ' ' + case when [data type] in ('int','datetime','smalldatetime') then [data type] else [data type] + '(' + convert(varchar(4),[length]) + ')' end + ','
from #table1

set @sql = @sql + ')'
set @sql = replace(@sql,',)',')')
print @sql
0
 
LVL 20

Author Comment

by:ihenry
ID: 10941647
How messy the code would be look like? which one is better from performance benefit between using and not cursor?
I'll accept cursor if there's no other option.
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10941649
I printed teh statement, you'll want to exec it
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10941663
No need to a cursor, your question sounded a bit different from what you wanted, so I dont think its a problem.

In almost any case, you can write code without a cursor. Some may be easy to code, some not. But the performance with no cursor is almost always faster. (like my excessive use of almost?)
0
 
LVL 3

Expert Comment

by:Frostbyte_Zero
ID: 10941721
If you are dealing with thousands of rows it will be messy.

But I have a solution. Use DTS ActiveX Scripting:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()


      Dim ConnectString

      ' Build the Connection String
      ConnectString = "PROVIDER=SQLOLEDB"
      ConnectString = ConnectString & ";SERVER=yourserver"
      ConnectString = ConnectString & ";UID=yourid"
      ConnectString = ConnectString & ";PWD=yourpassword"
      ConnectString = ConnectString & ";DATABASE=yourdatabase"

      Dim con, cmd, rst
      Dim SQL

      SQL = "SELECT * FROM yourtable"

      Set con = CreateObject("ADODB.Connection")
      con.ConnectionString = ConnectString
      con.Open

      Set cmd = CreateObject("ADODB.Command")
      cmd.ActiveConnection = con
      cmd.CommandText = SQL
      cmd.CommandTimeout = 0

      Set rst = CreateObject("ADODB.Recordset")      
      rst.Open cmd      

      Dim DDL
                              
      If rst.BOF And rst.EOF Then

      Else


            DDL = "CREATE TABLE yournewtable("

            Do While Not rst.EOF                        

                  If rst(1) = "varchar" Or rst(1) = "nvarchar" or rst(1) = "char" Then

                  DDL = DDL & Trim(rst(0)) & " " & Trim(rst(1)) & "(" & Trim(rst(2)) & "), "

                  Else

                  DDL = DDL & Trim(rst(0)) & " " & Trim(rst(1)) & ", "

                  End If

                  rst.MoveNext

            Loop

            DDL = Mid( DDL, 1, Len( DDL) - 2)

            DDL = DDL & ")"

      End if

      ' Clean up variables.
      Set rst = Nothing
      Set cmd = Nothing
      Set con = Nothing

      MsgBox DDL

      Main = DTSTaskExecResult_Success
End Function

The DDL variable will contain the SQL to cxreate the table. Just pass it back to the command object to execute the command. Voila! Instant Table. I tested it and it works perfectly.
0
 
LVL 20

Author Comment

by:ihenry
ID: 10942399
Hi all,

thanks for the code, i'm getting closer solving my problem. But it's leading me to one problem, though.

I'm able to execute this statement,
create table #temp1 (allow_Read int  default 0,allow_Add int  default 0,allow_Edit int  default 0,allow_Delete int  default 0 )
select * from #temp1
drop table #temp1


but i'm getting error "Invalid object name '#temp1'." when executing these lines:
declare @s nvarchar(4000)
set @s = 'create table #temp1 (allow_Read int  default 0,allow_Add int  default 0,allow_Edit int  default 0,allow_Delete int  default 0 )'
exec(@s)
select * from #temp1 --> at this line
drop table #temp1


If i change #temp1 to global temporary table ##temp1 it then works fine.
Any reason why to this problem?


0
 
LVL 3

Assisted Solution

by:Frostbyte_Zero
Frostbyte_Zero earned 300 total points
ID: 10942595
#temp1 = Session Dependent Temporary Table
after exec(@s) the session is terminated
0
 
LVL 4

Expert Comment

by:BulZeyE
ID: 10942744
Yes, the temp table that you are creating is being created but falls out of scope after the exec concludes. Then you try to reference it but it is already gone.

The code I posted is a valid T-SQL solution, have you tested it?
0
 
LVL 20

Author Comment

by:ihenry
ID: 10942814
Great, Thanks for all the help.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

604 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