一个比较难的行转列的问题，有测试脚本。

测试脚本如下：

USE [master]
GO
/****** Object:  Table [dbo].[类型表]    Script Date: 10/09/2011 03:59:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[类型表]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[类型表](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [类型组] [varchar](50) NULL,
    [类型名称] [varchar](50) NULL,
    [类别] [varchar](50) NULL,
CONSTRAINT [PK_类型表] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[类型表] ON
INSERT [dbo].[类型表] ([id], [类型组], [类型名称], [类别]) VALUES (1, N'A', N'生产日期', N'日期')
INSERT [dbo].[类型表] ([id], [类型组], [类型名称], [类别]) VALUES (2, N'A', N'库存类型', N'选择')
INSERT [dbo].[类型表] ([id], [类型组], [类型名称], [类别]) VALUES (3, N'A', N'产品状态', N'文本')
SET IDENTITY_INSERT [dbo].[类型表] OFF
/****** Object:  Table [dbo].[库存表]    Script Date: 10/09/2011 03:59:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[库存表]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[库存表](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [批号] [varchar](50) NULL,
    [类型ID] [bigint] NULL,
    [属性值] [varchar](50) NULL,
CONSTRAINT [PK_库存表] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[库存表] ON
INSERT [dbo].[库存表] ([id], [批号], [类型ID], [属性值]) VALUES (2, N'A01', 1, N'2011-06-08')
INSERT [dbo].[库存表] ([id], [批号], [类型ID], [属性值]) VALUES (3, N'A01', 2, N'1')
INSERT [dbo].[库存表] ([id], [批号], [类型ID], [属性值]) VALUES (4, N'A01', 3, N'已检验')
INSERT [dbo].[库存表] ([id], [批号], [类型ID], [属性值]) VALUES (5, N'A02', 1, N'2011-06-06')
INSERT [dbo].[库存表] ([id], [批号], [类型ID], [属性值]) VALUES (6, N'A02', 2, N'2')
INSERT [dbo].[库存表] ([id], [批号], [类型ID], [属性值]) VALUES (7, N'A02', 3, N'未检验')
SET IDENTITY_INSERT [dbo].[库存表] OFF
/****** Object:  Table [dbo].[类型属性表]    Script Date: 10/09/2011 03:59:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[类型属性表]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[类型属性表](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [类型ID] [bigint] NOT NULL,
    [属性] [varchar](50) NULL,
CONSTRAINT [PK_类型属性表] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[类型属性表] ON
INSERT [dbo].[类型属性表] ([id], [类型ID], [属性]) VALUES (1, 2, N'良品')
INSERT [dbo].[类型属性表] ([id], [类型ID], [属性]) VALUES (2, 2, N'次品')
SET IDENTITY_INSERT [dbo].[类型属性表] OFF
/****** Object:  ForeignKey [FK_类型属性表_类型表]    Script Date: 10/09/2011 03:59:20 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_类型属性表_类型表]') AND parent_object_id = OBJECT_ID(N'[dbo].[类型属性表]'))
ALTER TABLE [dbo].[类型属性表]  WITH CHECK ADD  CONSTRAINT [FK_类型属性表_类型表] FOREIGN KEY([类型ID])
REFERENCES [dbo].[类型表] ([id])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_类型属性表_类型表]') AND parent_object_id = OBJECT_ID(N'[dbo].[类型属性表]'))
ALTER TABLE [dbo].[类型属性表] CHECK CONSTRAINT [FK_类型属性表_类型表]
GO


我用如下行转列的代码：

declare @sql varchar(8000)
set @sql ='  
select
c.批号
'
select @sql = @sql + ' , max(case [类型名称] when ''' + ltrim(a.[类型名称]) + ''' then ltrim([属性值]) else null end) [' + a.[类型名称] + ']'
from (select distinct 类型名称 from 类型表 ) as a
set @sql =@sql + ' from dbo.库存表 c , dbo.类型表 b where b.id=c.类型ID
group by c.批号
'
exec(@sql)



得到了如下结果：
批号 产品状态 库存类型 生产日期
A01 已检验 1 2011-06-08
A02 未检验 2 2011-06-06



我现在的问题是：库存类型下的值，仍然显示的属性表的ID，而不是所对应真正的值。怎么显示真正值出来呢（当然是从类型属性表里取）？


期望结果如下：
批号 产品状态 库存类型 生产日期
A01 已检验 良品 2011-06-08
A02 未检验 次品 2011-06-06


当然最后提醒下，类型表里的行是动态的，所以转列的时候当然也是动态的，不会是定死的三种。

在关联一次，将属性值读取出来即可。