Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Transpose RecordSet

Posted on 2004-09-15
3
Medium Priority
?
816 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 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

886 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