SQL Server 2000中多列的动态数据透视 [英] Dynamic Pivot for multiple columns in SQL Server 2000
问题描述
我知道,在其他论坛上发布相同的问题链接是一种不好的礼节,但这是我的问题链接:
I know, it is bad etiquette to post the same question link in other forum, but here it is my question link :
http://www.sqlservercentral.com/Forums/Topic1474579-8- 1.aspx
Microsoft SQL Server 2000-8.00.2039(Intel X86)2005年5月3日23:18:38版权所有(c)1988-2003 Windows NT 5.2上的Microsoft Corporation Enterprise Edition(Build 3790:Service Pack 2)
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
表结构和示例插入语句:
Table structure and sample insert statements :
create table consumption (
code varchar(6),
prodname varchar(50),
department varchar(20),
netqty decimal(10,2),
netmrp decimal(10,2)
)
insert into consumption values ('U00180','USG JELLY IM-K','SONO',11.4,130.40)
insert into consumption values ('U00280','UNIDINE 1 LITRE SOLUTION','AKD',1.4,10.40)
insert into consumption values ('V02401','VOLINI GEL 50GM','PHYSIOTHERAPY',8,15)
insert into consumption values ('V02402','X RAY FILM DIGITAL 14"X 17"','MRI',3,26.40)
insert into consumption values ('U00380','TROPAC P DROPS ','AKD',21.46,56.78)
insert into consumption values ('U00380','TROPAC P DROPS ','AKD',10,10)
表格数据:
code prodname department netqty netmrp
U00180 USG JELLY IM-K SONO 11.40 130.40
U00280 UNIDINE 1 LITRE SOLUTION AKD 1.40 10.40
V02401 VOLINI GEL 50GM PHYSIOTHERAPY 8.00 15.00
V02402 X RAY FILM DIGITAL 14"X 17" MRI 3.00 26.40
U00380 TROPAC P DROPS AKD 21.46 56.78
U00380 TROPAC P DROPS AKD 10.00 10.00
必需的输出,请:
CODE PRODNAME AKD MRI PHYSIOTHERAPY SONO
NET QTY NET MRP NET QTY NET MRP NET QTY NET MRP NET QTY NET MRP
U00180 USG JELLY IM-K 11.40 130.40
U00280 UNIDINE 1 LITRE SOLUTION 1.40 10.40
U00380 TROPAC P DROPS 31.46 66.78
V02401 VOLINI GEL 50GM 8.00 15.00
V02402 X RAY FILM DIGITAL 14"X 17" 3.00 26.40
很抱歉,以这种方式发布问题,但是如果有人可以通过查看上面的链接来帮助我,我将非常感谢您的帮助.
I am sorry, to post the question in such a way, but if anyone can please help me by looking the above link, I shall be thankful for your help.
仅因为我认为在该论坛中活动较少,所以我在这里尝试.
Just because I think in that forum, there is less activity, so I am trying here.
更新:问题已解决,我在上面的同一链接中发布了解决方案.感谢所有参与并帮助我的人.
Update:Question is solved and I posted the solution in the same above link. Thanks to all who participated and helped me.
推荐答案
很难在SQL2000中进行透视,但这是我创建的一些示例代码,用于使用游标对多个列进行透视
its a pain to pivot in SQL2000 but here is some sample code that I created to pivot multiple columns using cursors
DECLARE @SQL nvarchar(4000)
DECLARE @TaskName nvarchar(100)
SET NOCOUNT ON
CREATE TABLE #tblTLine (
[DT] varchar(200)
)
CREATE TABLE #tblTasks (
[Tasks] varchar(200)
)
INSERT INTO #tblTasks (
[Tasks]
)
select DISTINCT
Name
from #Pivot
INSERT INTO #tblTLine (
[DT]
)
select DISTINCT
[DT]
from #Pivot
ORDER BY DT
--WHERE Active = 1
-- Build Table
DECLARE cur CURSOR FOR
select DISTINCT
[Tasks]
from #tblTasks
OPEN cur
FETCH NEXT FROM cur INTO @TaskName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER TABLE #tblTLine ADD [' + @TaskName + '] nchar(1) NULL'
EXEC (@SQL)
SET @SQL = ''
SET @SQL = 'UPDATE #tblTLine SET [' + @TaskName + '] = ''0'''
EXEC (@SQL)
FETCH NEXT FROM cur INTO @TaskName
END
CLOSE cur
DEALLOCATE cur
-- Update Table
DECLARE @SQLUpdate nvarchar(4000)
DECLARE @Time nvarchar(100)
DECLARE @Name nvarchar(100)
DECLARE @Active nchar(1)
DECLARE curUpdate CURSOR FOR
SELECT
[DT],
[Name],
[Active]
FROM #Pivot
WHERE Active = 1
OPEN curUpdate
FETCH NEXT FROM curUpdate INTO @Time, @Name, @Active
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLUpdate = 'UPDATE #tblTLine SET [' + @Name + '] = ''1'' WHERE [DT] = ''' + @Time + ''''
EXEC (@SQLUpdate)
FETCH NEXT FROM curUpdate INTO @Time, @Name, @Active
END
CLOSE curUpdate
DEALLOCATE curUpdate
SET NOCOUNT OFF
这篇关于SQL Server 2000中多列的动态数据透视的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!