关于SQL Server 行与列的转换................

Question

哪位高手能教我如何使用pivot和unpivot关键字？
我是这样用的：
select col1,col2,col3 from test unpivot(col1 for col2 in([col1,col2,col3])) as upvt但是报错，in([col1,col2,col3]))这一段语句报错。
有谁能告诉我为什么报错，教教我怎么用这两个关键字。

Answer 1

declare @t table ( id int,valuelist varchar(200))insert @t  select 1,'1,43,3,2'insert @t  select 2,'233,100,4455,323,223'  --方法一;with tb as(select id,value from @tcross apply(select xmlcode=CAST('<row>'+replace(valuelist,',','</row><row>')+'</row>' as xml)) c0cross apply xmlcode.nodes('/row')c1(xmlcode1)cross apply (select value=c1.xmlcode1.value('.','int')) c2) select id,replace((select value as 'data()'  from tb  where a.id=id order by value for xml path('')),' ',',')  as datalistfrom @t a    --方法二select id,replace(xmlcode1.value('.','varchar(200)'),' ',',') as datalist from @tcross apply(select xmlcode=CAST('<row>'+replace(valuelist,',','</row><row>')+'</row>' as xml)) c0cross apply(select xmlcode1=xmlcode.query('                                <e> {   for $i in /row                                       order by  $i                                       return data($i)                                   }                                    </e>                                    ')) c1

Answer 2

CREATE TABLE dbo.pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO dbo.pvt VALUES 
 (1,4,3,5,4,4)
,(2,4,1,5,5,5)
,(3,4,3,5,4,4)
,(4,4,2,5,5,4)
,(5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
    (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
    FROM dbo.pvt) AS p
UNPIVOT
    (Orders FOR Employee IN 
        (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO

Answer 3

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO
以下为部分结果集。VendorID Employee Orders---------- ---------- ------1          Emp1       41          Emp2       31          Emp3       51          Emp4       41          Emp5       42          Emp1       42          Emp2       12          Emp3       52          Emp4       52          Emp5       5...请注意，UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 会执行一次聚合，从而将多个可能的行合并为输出中的单个行。而 UNPIVOT 不会重现原始表值表达式的结果，因为行已经被合并了。另外，UNPIVOT 的输入中的空值不会显示在输出中，而在执行 PIVOT 操作之前，输入中可能有原始的空值。

Answer 4

;with cte as
(
select 'A' AS ID,'U' as type,1 AS N
union all
select 'A' ,'V' ,2
union all
select 'A','V',3
union all
select 'B','V',3
)select * from 
(SELECT ID,TYPE FROM CTE)C  ---保证只有分组列、聚合列
PIVOT (
COUNT(TYPE)         ---聚合
FOR TYPE IN(U,V)    
)T/*
ID   U           V
---- ----------- -----------
A    1           2
B    0           1
*/----->UNPIVOT 
;WITH U_CTE AS
(
 SELECT 'A' AS ID,1 AS U,2 AS V
 UNION ALL
 SELECT 'B',0,1
)SELECT * FROM 
(SELECT ID,U,V FROM U_CTE)U  ---保证只有分组列，聚合列
UNPIVOT
(
NUM  ---转换后存放列值的列名
FOR TYPE IN (U,V)  ---转换后存放原列名的列名
)T/*
ID   NUM         TYPE
---- ----------- ------------------------------------------------------
A    1           U
A    2           V
B    0           U
B    1           V
*/;with s as
(
select 'A' ID,    1 NUM ,   'U' TYPE  UNION ALL
SELECT 'A' ,   2 ,          'V' UNION ALL
SELECT 'B' ,   0 ,          'U' UNION ALL
SELECT 'B' ,   1 ,          'V'  
)
SELECT * FROM S
PIVOT(
MAX(NUM)
FOR TYPE IN (U,V)
)T
ID   U           V
---- ----------- -----------
A    1           2
B    0           1(2 行受影响)

Answer 5

sql2000 不支持此关键字没有使用分组函数如max(),sum()等select col1,col2,col3 from test unpivot(max() for col2 in([col1,col2,col3])) as upvt

Answer 6

我也是这样做的：
select psize,pcolor,pstore from Product unpivot(pcolor for psize in(psize,pcolor,pstore)) unpvt;
但是执行的时候报错：
消息 207，级别 16，状态 1，第 1 行
Invalid column name 'L'.
消息 207，级别 16，状态 1，第 1 行
Invalid column name 'M'.
消息 207，级别 16，状态 1，第 1 行
Invalid column name 'S'.
消息 265，级别 16，状态 1，第 1 行
The column name "pcolor" specified in the UNPIVOT operator conflicts with the existing column name in the UNPIVOT argument.
消息 265，级别 16，状态 1，第 1 行
The column name "psize" specified in the UNPIVOT operator conflicts with the existing column name in the UNPIVOT argument.
消息 8156，级别 16，状态 1，第 1 行
The column 'pcolor' was specified multiple times for 'unpvt'.

Answer 7

USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID
 以下是部份結果集： 複製程式碼 
VendorID    Emp1        Emp2        Emp3        Emp4        Emp5
1           4           3           5           4           4
2           4           1           5           5           5
3           4           3           5           4           4
4           4           2           5           5           4
5           5           1           5           5           5
 

Answer 8

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO
 以下是部份結果集： 複製程式碼 
VendorID   Employee   Orders
1      Emp1         4
1      Emp2         3
1      Emp3         5
1      Emp4         4
1      Emp5         4
2      Emp1         4
2      Emp2         1
2      Emp3         5
2      Emp4         5
2      Emp5         5
...
 

Answer 9

psize   pcolor         pstore
---------------------------
M Red        220
M Blue       12
L Black      30
L Yellow     45
M Yellow     10
S Red        100
S Yellow     100
S Black      20
L Blue       30就是一张单表。
执行的语句是：
1）select psize,pcolor,pstore from Product unpivot(pcolor for psize in([L],[M],[S])) unpvt;
2）select psize,pcolor,pstore from Product unpivot(pcolor for psize in([psize],[pcolor],[pstore])) unpvt;
3）select psize,pcolor,pstore from Product unpivot(pcolor for psize in(psize,pcolor,pstore)) unpvt;
4）select psize,pcolor,pstore from Product unpivot(pcolor for psize in('L','M','S')) unpvt;执行以上语句均报错。都是报找不到那些列。

Answer 10

数据如下：
psize  pcolor    pstore
---------------------------
M      Red      220
M      Blue     12
L      Black    30
L      Yellow   45
M      Yellow   10
S      Red      100
S      Yellow   100
S      Black    20
L      Blue     30