I have a need to change the columns in a table to rows with values
/****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [Test] (
[UserID] [int] NULL ,
[NurseID] [int] NULL ,
[NurseID2] [int] NULL ,
[NurseID3] [int] NULL ,
[ReceptionID] [int] NULL ,
[OfficemanID] [int] NULL ,
[NurseTrainID] [int] NULL ,
[ResidentTrainID] [int] NULL ,
[ResidentTrainID2] [int] NULL ,
[ResidentTrainID3] [int] NULL
) ON [PRIMARY]
END
Insert into test
(UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
values
(1,3,9,4,7)
Select * from Test would give
UserID NurseID, NurseID2
1 3 9
and I need to transform to using SQL2000
Description Users
UserID 1
NurseID 3
NurseID2 9
I may not need the description column
Thanks for the help
Stephen K. MiyasatoHi Stephen,
2005 allows using UNPIVOT clause. Not sure about 2000 though..
http://msdn2.microsoft.com/en-us/library/ms177410.aspx
"Stephen K. Miyasato" wrote:
> I have a need to change the columns in a table to rows with values
> /****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
> ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [Test] (
> [UserID] [int] NULL ,
> [NurseID] [int] NULL ,
> [NurseID2] [int] NULL ,
> [NurseID3] [int] NULL ,
> [ReceptionID] [int] NULL ,
> [OfficemanID] [int] NULL ,
> [NurseTrainID] [int] NULL ,
> [ResidentTrainID] [int] NULL ,
> [ResidentTrainID2] [int] NULL ,
> [ResidentTrainID3] [int] NULL
> ) ON [PRIMARY]
> END
> Insert into test
> (UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
> values
> (1,3,9,4,7)
> Select * from Test would give
> UserID NurseID, NurseID2
> 1 3 9
> and I need to transform to using SQL2000
> Description Users
> UserID 1
> NurseID 3
> NurseID2 9
> I may not need the description column
> Thanks for the help
> Stephen K. Miyasato
>
>|||If you were using SQL Server 2005, you could use an UNPIVOT statement.
In this case, I think you'll just have to use a series of UNION ALL
statements to transform the data, as in:
select
'UserID' as Description,
UserID as Users
from test
UNION ALL
select
'NurseID' as Description,
NurseID as Users
from test
UNION ALL
select
'NurseID2' as Description,
NurseID2 as Users
from test|||If you were using SQL Server 2005, you could use an UNPIVOT statement.
In this case, I think you'll just have to use a series of UNION ALL
statements to transform the data, as in:
select
'UserID' as Description,
UserID as Users
from test
UNION ALL
select
'NurseID' as Description,
NurseID as Users
from test
UNION ALL
select
'NurseID2' as Description,
NurseID2 as Users
from test|||For SS2000, you can refer to the following.
- How to rotate a table in SQL Server
http://support.microsoft.com/defaul...kb;en-us;175574
Martin C K Poon
Senior Analyst Programmer
====================================
"Stephen K. Miyasato" <miyasat@.flex.com> bl
news:%23d67IiofGHA.4864@.TK2MSFTNGP05.phx.gbl g...
> I have a need to change the columns in a table to rows with values
> /****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
> ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [Test] (
> [UserID] [int] NULL ,
> [NurseID] [int] NULL ,
> [NurseID2] [int] NULL ,
> [NurseID3] [int] NULL ,
> [ReceptionID] [int] NULL ,
> [OfficemanID] [int] NULL ,
> [NurseTrainID] [int] NULL ,
> [ResidentTrainID] [int] NULL ,
> [ResidentTrainID2] [int] NULL ,
> [ResidentTrainID3] [int] NULL
> ) ON [PRIMARY]
> END
> Insert into test
> (UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
> values
> (1,3,9,4,7)
> Select * from Test would give
> UserID NurseID, NurseID2
> 1 3 9
> and I need to transform to using SQL2000
> Description Users
> UserID 1
> NurseID 3
> NurseID2 9
> I may not need the description column
> Thanks for the help
> Stephen K. Miyasato
>|||Thanks very much,
That is what I was looking for.
Stephen
"dterrie" <dterrie@.axiomadvisors.net> wrote in message
news:1148404941.573763.96380@.i39g2000cwa.googlegroups.com...
> If you were using SQL Server 2005, you could use an UNPIVOT statement.
> In this case, I think you'll just have to use a series of UNION ALL
> statements to transform the data, as in:
> select
> 'UserID' as Description,
> UserID as Users
> from test
> UNION ALL
> select
> 'NurseID' as Description,
> NurseID as Users
> from test
> UNION ALL
> select
> 'NurseID2' as Description,
> NurseID2 as Users
> from test
>
