How do you reorder the ORDINAL_POSITION of a table?

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
TraciShultzAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
chapmandewConnect With a Mentor Commented:
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
 
chapmandewCommented:
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
 
TraciShultzAuthor Commented:
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
 
TraciShultzAuthor Commented:
Thanks so much. this really helped me out of a jam...
0
 
TraciShultzAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.