如何多列行转多列

Question

sqlserver2005里面一列中的多行转列用pivot，如果是多列多行，如何做呢
customer Ismadeinchina Strategy FMaker_Brand  Quantity
1             true                   A             M                       100
1             false                  B             F                        200 
如上所示，（Ismadeinchina Strategy FMaker_Brand）每列有二种类型。组合起来有8列，如何装换为
customer  trueAM  trueAF  trueBM trueBF  falseAM  falseAF  falseBM  falseBF
1                  100   0           0         0           0          0            0          200
这种形式？

Answer 1

這樣的設計還真是差，唉

把 Ismadeinchina,Strategy, FMaker_Brand生成動態列再處理

use Tempdb
go
--> -->
 
if not object_id(N'Tempdb..#T2') is null
    drop table #T2
Go
Create table #T2([customer] int,[Ismadeinchina] bit,[Strategy] nvarchar(1),[FMaker_Brand] nvarchar(1),[Quantity] int)
Insert #T2
select 1,1,N'A',N'M',100 union all
select 1,0,N'B',N'F',200
Go
declare @s1 nvarchar(4000),@s2 nvarchar(4000)
select @s1='',@s2=''
select @s1=@s1+',[true'+[Strategy]+[FMaker_Brand]+']=sum(case when [Strategy]='''+[Strategy]+''' and [FMaker_Brand]='''+[FMaker_Brand]+''' and [Ismadeinchina]=1 then [Quantity] else 0 end)',
    @s2=@s2+',[false'+[Strategy]+[FMaker_Brand]+']=sum(case when [Strategy]='''+[Strategy]+''' and [FMaker_Brand]='''+[FMaker_Brand]+''' and [Ismadeinchina]=0 then [Quantity] else 0 end)'
from
(Select distinct [Strategy] from #T2)a,(select distinct [FMaker_Brand] from #T2)b

exec(' select [customer]'+ @s1+@s2+' from #T2 group by [customer]')

/*
customer    trueAF      trueAM      trueBF      trueBM      falseAF     falseAM     falseBF     falseBM
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           0           100         0           0           0           0           200         0
*/

---

ROY WU(吳熹 )

Answer 2

可以考虑直接在程序端用专门的控件去做

展示数据的处理放在数据库端处理并不是好事.

Answer 3

這樣的設計還真是差，唉

把 Ismadeinchina,Strategy, FMaker_Brand生成動態列再處理

use Tempdb

go

--> -->

 

if not object_id(N'Tempdb..#T2') is null

    drop table #T2

Go

Create table #T2([customer] int,[Ismadeinchina] bit,[Strategy] nvarchar(1),[FMaker_Brand] nvarchar(1),[Quantity] int)

Insert #T2

select 1,1,N'A',N'M',100 union all

select 1,0,N'B',N'F',200

Go

declare @s1 nvarchar(4000),@s2 nvarchar(4000)

select @s1='',@s2=''

select @s1=@s1+',[true'+[Strategy]+[FMaker_Brand]+']=sum(case when [Strategy]='''+[Strategy]+''' and [FMaker_Brand]='''+[FMaker_Brand]+''' and [Ismadeinchina]=1 then [Quantity] else 0 end)',

    @s2=@s2+',[false'+[Strategy]+[FMaker_Brand]+']=sum(case when [Strategy]='''+[Strategy]+''' and [FMaker_Brand]='''+[FMaker_Brand]+''' and [Ismadeinchina]=0 then [Quantity] else 0 end)'

from

(Select distinct [Strategy] from #T2)a,(select distinct [FMaker_Brand] from #T2)b



exec(' select [customer]'+ @s1+@s2+' from #T2 group by [customer]')



/*

customer    trueAF      trueAM      trueBF      trueBM      falseAF     falseAM     falseBF     falseBM

----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------

1           0           100         0           0           0           0           200         0

*/

---

ROY WU(吳熹 )

呵呵，和设计没有关系，我只是举个例子研究一下，看看有没有好的方法，谢谢

Answer 4

可以考虑直接在程序端用专门的控件去做

展示数据的处理放在数据库端处理并不是好事.

我用reportingservice做的报表，不知道哪个地方能写代码，一般我的做法就是用存储过程把数据全部取好，然后直接展示