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

x
?
Solved

How do you reorder the ORDINAL_POSITION of a table?

Posted on 2008-06-23
5
Medium Priority
?
409 Views
Last Modified: 2010-04-21
I have create a table that was created from a pivot sp that all works great the problem is I need to write a generic select that returns my pivot table with the first 2 columns in position 1 and 2. This is because Column1 and 2 are guarenteed,

See data below:
my fields are as follows:
Field              Ordinal Position
RackInfoID      1
Library A       2
Library B      3
Not Libraried      4
Saved Library      5

My data looks like this:
RackInfoID       Library A Library B    Not Libraried    Saved Library
ABC009407      NULL        NULL                   1                       NULL
ABC009408       NULL        NULL                  NULL           1    
ABC009409      NULL        NULL                   1                      NULL

So I want to  Return Column in this Order:
RackInfoID      1
Not Libraried      2
Library A       3
Library B      4
Saved Library      5
0
Comment
Question by:TraciShultz
[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
  • 2
5 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21848441
To change the ordinal positions of the columns in the table, you'll need to drop it and recreate it.  SQL Server doesn't have any internal constructs to reorder by columns.  When you do it in management studio, it creates a new table for you and swaps them out.
0
 

Author Comment

by:TraciShultz
ID: 21855708
I am usiong brut forse to change the ordinal position , I created the following SP. Which cretes a temp table and and through a cursor I am trying to add the additional fields. but I get the following Error:
"Only UNIQUE or PRIMARY KEY constraints can be created on computed columns, while CHECK, FOREIGN KEY, and NOT NULL constraints require that computed columns be persisted."
Any idea's would be great.


ALTER PROCEDURE [dbo].[LibraryListEdit_CreateTempLibraryNewPivot]
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      DECLARE @ColumnName char(100)
      DECLARE @SQL VARCHAR(8000)
                  
      if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempLibraryNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      drop table [dbo].[TempLibraryNew]

      CREATE TABLE [dbo].[TempLibraryNew](
            [RackInfoID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
            [Not Libraried] [int] NULL
      ) ON [PRIMARY]

            
      DECLARE c1 CURSOR READ_ONLY
      FOR
      SELECT     TOP (100) PERCENT c.name AS ColumnName
      FROM         sys.sysobjects AS o INNER JOIN
                                      sys.syscolumns AS c ON o.id = c.id
      WHERE     (o.type = 'U') AND (o.name = N'TempLibrary') AND (c.name <> N'RackInfoID') AND (c.name <> 'Not Libraried')
      ORDER BY c.colorder

      OPEN c1

      FETCH NEXT FROM c1
      INTO @ColumnName

      WHILE @@FETCH_STATUS = 0
      BEGIN
      SET @SQL = 'ALTER TABLE [dbo].[TempLibraryNew] ADD [' +RTRIM(@ColumnName ) +'] AS int NULL'
            PRINT @SQL
      EXECUTE (@SQL)
            FETCH NEXT FROM c1
            INTO @ColumnName
      END

      CLOSE c1
      DEALLOCATE c1
END
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 21855807
need to take out your AS statement:

'ALTER TABLE [dbo].[TempLibraryNew] ADD [' +RTRIM(@ColumnName ) +'] AS int NULL'

should be:

'ALTER TABLE [dbo].[TempLibraryNew] ADD [' +RTRIM(@ColumnName ) +'] int NULL'
0
 

Author Closing Comment

by:TraciShultz
ID: 31469864
Thanks so much. this really helped me out of a jam...
0
 

Author Comment

by:TraciShultz
ID: 21856056
Here is  the final solution.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author:            TShultz
-- Create date:
-- Description:      
-- =============================================
ALTER PROCEDURE [dbo].[LibraryListEdit_CreateTempLibraryNewPivot]
      -- Add the parameters for the stored procedure here
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      DECLARE @ColumnName char(100)
      DECLARE @SQL VARCHAR(8000)
                  
      if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempLibraryNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      drop table [dbo].[TempLibraryNew]

      CREATE TABLE [dbo].[TempLibraryNew](
            [RackInfoID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
            [Not Libraried] [int] NULL
      ) ON [PRIMARY]


      DECLARE c1 CURSOR READ_ONLY
      FOR
      SELECT     TOP (100) PERCENT c.name AS ColumnName
      FROM         sys.sysobjects AS o INNER JOIN
                                      sys.syscolumns AS c ON o.id = c.id
      WHERE     (o.type = 'U') AND (o.name = N'TempLibrary') AND (c.name <> N'RackInfoID') AND (c.name <> 'Not Libraried')
      ORDER BY c.colorder

      OPEN c1

      FETCH NEXT FROM c1
      INTO @ColumnName

      WHILE @@FETCH_STATUS = 0
      BEGIN
      SET @SQL = 'ALTER TABLE [dbo].[TempLibraryNew] ADD [' +RTRIM(@ColumnName ) +'] int NULL'
            PRINT @SQL
      EXECUTE (@SQL)
            FETCH NEXT FROM c1
            INTO @ColumnName
      END

      CLOSE c1
      DEALLOCATE c1
      
      INSERT INTO TempLibraryNew
      SELECT     dbo.TempLibrary.*
      FROM         dbo.TempLibrary

      if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempLibrary]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      drop table [dbo].[TempLibrary]

      SELECT * INTO TempLibrary
      FROM TempLibraryNew

      if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempLibraryNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      drop table [dbo].[TempLibraryNew]
      

END
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

688 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