Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Transpose RecordSet

Posted on 2004-09-15
3
Medium Priority
?
807 Views
Last Modified: 2012-08-13
I have a sql table with 2 columns "A" and "B".
I want creat one Table that all Record in Columns "A" to change columns and all record in "B" to become Record of new columns

Help me.

Thank you.
0
Comment
Question by:doanket
[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 Comments
 
LVL 9

Expert Comment

by:solution46
ID: 12062403
do you have any sample data, or is this homework?
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12062473
How many records in your table ?
I think you can't have more than 1024 columns in a SQL Server table.
Also be careful with the maximum row size = 1 physical page = 8060 bytes.
0
 
LVL 2

Accepted Solution

by:
vidnan123 earned 800 total points
ID: 12063023
Here is one method that I thought for your requirement, though a roundabout one..I would definitely like to see a simpler solution..Anyways, here is my solution...

I have a table called ID with 2 columns ID1 and ID2 with data as follows.

ID1                                                ID2                                                
-------------------------------------------------- --------------------------------------------------
1                                                  11
2                                                  22
3                                                  33
4                                                  44

This query will create a table called IDTEMP with values as follows.


1           2           3           4          
----------- ----------- ----------- -----------
11          22          33          44

Here is the query....

-- First Create the table with column 1 rows as columns for the table
DECLARE @Col1 int,
      @sql nvarchar(1000)

DECLARE curA CURSOR FOR
SELECT distinct ID1 FROM ID

OPEN curA
FETCH NEXT FROM curA INTO @Col1

WHILE @@FETCH_STATUS=0
BEGIN
      if exists (select * from dbo.sysobjects where id = object_id(N'[IDTEMP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      BEGIN
            SELECT @sql = 'ALTER TABLE IDTEMP ADD [' + convert(varchar,@Col1) + '] int'
            exec sp_executesql @sql
      END
      ELSE
      BEGIN            
            
            SELECT @sql = 'CREATE TABLE IDTEMP([' + convert(varchar,@Col1)+ '] int)'
            exec sp_executesql @sql
      END      

FETCH NEXT FROM curA INTO @Col1
END

CLOSE curA
DEALLOCATE curA

--Next, update the new table with column 2 rows as column values

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @FormatColName nvarchar(128)

SELECT @TableName='[dbo].[IDTEMP]'
SET @ColumnName = ''

WHILE (@ColumnName IS NOT NULL)
BEGIN
      SET @ColumnName =
      (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM       INFORMATION_SCHEMA.COLUMNS
            WHERE             TABLE_SCHEMA      = PARSENAME(@TableName, 2)
                  AND      TABLE_NAME      = PARSENAME(@TableName, 1)
                  AND      QUOTENAME(COLUMN_NAME) > @ColumnName
      )

      SELECT @FormatColName =REPLACE(REPLACE(@ColumnName,'[',''),']','')

      IF (SELECT COUNT(1) FROM IDTEMP) = 0
      BEGIN
            select @sql='INSERT INTO IDTEMP(' + @ColumnName + ') SELECT ID2 FROM ID WHERE ID1 = '+@FormatColName
            exec sp_executesql @sql      
      END
      ELSE
      BEGIN
            select @sql='UPDATE IDTEMP SET ' + @ColumnName + '= (SELECT ID2 FROM ID WHERE ID1 = '+@FormatColName + ')'
            exec sp_executesql @sql      
      END      
END

Hope this helps!

0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

715 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