Solved

Transpose RecordSet

Posted on 2004-09-15
3
796 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 200 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

623 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