感觉有点难度的SQL语句

[萧霖]

OrderID StyleID SizeName StyleColor StyleBrand StyleListQty
----------- ------- -------------------- ------------------------------ -------------------- ------------
16 2 1 Black 300
16 2 3 Black 300
16 2 5 Black 200
16 2 7 Black 800
16 2 9 Black 200
16 2 11 Black 200

（所影响的行数为 6 行）


有如上数据纪录，有没有什么语句可以实现如下结果？

OrderID StyleID 1 3 5 7 9 11 StyleColor StyleBrand
----------- ------- --- --- --- --- --- --- -------------------- ------------
16 2 300 300 200 800 200 200 Black

谢谢帮忙！

[wuxinyuzhu]

我只是进行了行和列的转换 我没有使用聚合函数 不用加 GROUP BY 
要是需要使用MIN MAX SUM AVG ...
就改一下 例如：求和
select orderid,styeid,sum(case sizename when 1 then stylecolor else ''end ) '1',
                      sum(case sizename when 3then stylecolor else ''end ) '3',
                      sum(case sizename when 5then stylecolor else ''end ) '5',
                      sum(case sizename when 7then stylecolor else ''end ) '7',
                      sum(case sizename when 9then stylecolor else ''end ) '9',
                      sum(case sizename when 11then stylecolor else ''end ) '11',
stylebrand
from tbl group by orderid,styeid,stylebrand 

[neversaydie518]

楼上正解

[samfeng_2003]

练个手!:)

CREATE TABLE T
(
OrderID INT,
StyleID INT,
SizeName VARCHAR(20),
StyleColor VARCHAR(20),
StyleBrand VARCHAR(20),
StyleListQty INT
)

INSERT INTO T
SELECT 16,2,1,'Black',NULL, 300 UNION ALL
SELECT 16,2,3,'Black',NULL, 300 UNION ALL
SELECT 16,2,5,'Black',NULL, 200 UNION ALL
SELECT 16,2,7,'Black',NULL, 800 UNION ALL
SELECT 16,2,9,'Black',NULL, 200 UNION ALL
SELECT 16,2,11,'Black',NULL, 200

DECLARE @sql VARCHAR(8000)
SELECT @sql = 'SELECT OrderID, StyleID'
SELECT @sql = @sql + ',['+SizeName+'] = SUM(CASE WHEN SizeName = '''+SizeName+''' THEN StyleListQty END)'
FROM T GROUP BY SizeName
EXEC( @sql + ',StyleColor,StyleBrand FROM T GROUP BY OrderID,StyleID,StyleColor,StyleBrand ORDER BY OrderID,StyleID' )

DROP TABLE T

[pt1314917]

create table tb (OrderID int,StyleID int,SizeName int,StyleColor varchar(20),StyleBrand varchar(50),StyleListQty int)
insert into tb(OrderID,styleid,sizename,stylecolor,stylelistqty) select 16 , 2 ,1,'Black',300 
insert into tb(OrderID,styleid,sizename,stylecolor,stylelistqty)  select 16,2   ,    3   ,                 'Black'         ,                                      300 
insert into tb(OrderID,styleid,sizename,stylecolor,stylelistqty)  select 16 ,         2   ,    5      ,              'Black' ,                                              200 
insert into tb(OrderID,styleid,sizename,stylecolor,stylelistqty)  select 16 ,         2   ,    7      ,              'Black'   ,                                            800 
insert into tb(OrderID,styleid,sizename,stylecolor,stylelistqty)  select 16  ,        2   ,    9     ,               'Black'   ,                                            200 
insert into tb(OrderID,styleid,sizename,stylecolor,stylelistqty)  select 16   ,       2   ,   11     ,              'Black'    ,                                           200 

declare @sql varchar(1000)
set @sql='select orderid,styleid'
select @sql=@sql+',['+cast(SizeName as varchar)+']=max(case SizeName when '''+cast(SizeName as varchar)+''' then StyleListQty else 0 end)' from tb 
set @sql=@sql+',stylecolor,stylebrand from tb group by orderid,styleid,stylecolor,stylebrand'
exec(@sql)

[wuxinyuzhu]

select orderid,styeid,(case sizename when 1 then stylecolor else ''end ) '1',
                      (case sizename when 3then stylecolor else ''end ) '3,
                      (case sizename when 5then stylecolor else ''end ) '5,
                      (case sizename when 7then stylecolor else ''end ) '7,
                      (case sizename when 9then stylecolor else ''end ) '9,
                      (case sizename when 11then stylecolor else ''end ) '11',
stylebrand
from tbl

[wuxinyuzhu]

--刚才有点手误
select orderid,styeid,(case sizename when 1 then stylecolor else ''end ) '1',
                      (case sizename when 3then stylecolor else ''end ) '3',
                      (case sizename when 5then stylecolor else ''end ) '5',
                      (case sizename when 7then stylecolor else ''end ) '7',
                      (case sizename when 9then stylecolor else ''end ) '9',
                      (case sizename when 11then stylecolor else ''end ) '11',
stylebrand
from tbl

[nobody@noone]

楼上的少了group by orderid,styeid，stylebrand

[sunhonglei2004]

select OrderID,StyleID,sum(case SizeName when 1 then StyleBrand end) as '1',
sum(case SizeName when 3 then StyleBrand end )as '3',
sum(case SizeName when 5 then StyleBrand else ''end ) '5',
sum(case SizeName when 7 then StyleBrand else ''end ) '7',
sum(case SizeName when 9 then StyleBrand else ''end ) '9',
sum(case SizeName when 11 then StyleBrand else ''end ) '11',
StyleColor, StyleListQty
from ta
group by orderID,StyleID,StyleColor, StyleListQty

[萧霖]

谢谢1314917。　

您的答案有实际作用。

[pt1314917]

不用谢，互相学习罢了``