Solved

Transpose RecordSet

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

735 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