Solved

Transpose RecordSet

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now