一个行转列的问题，在线等

[victorcai2006]

DECLARE @TB TABLE (TID VARCHAR(5),CLASSID VARCHAR(5),KINDID VARCHAR(5),PRICE NUMERIC(9,2))

INSERT INTO @TB
SELECT '01','1','A',92.10 UNION ALL
SELECT '01','2','C',10.00 UNION ALL
SELECT '01','1','A',1.00 UNION ALL
SELECT '02','2','E',8.10

SELECT * FROM @TB

/*
想要实现的结果：
TID 1_KIND 1_PRICE 2_KIND 2_PRICE
------ ------ ------ ------ ------
01 A 93.10 C 10.00
02 0.00 E 8.10
*/

[-晴天]

不是回过了么!

[-晴天]

DECLARE @TB TABLE (TID VARCHAR(5),CLASSID VARCHAR(5),KINDID VARCHAR(5),PRICE NUMERIC(9,2))

INSERT INTO @TB
SELECT '01','1','A',92.10 UNION ALL
SELECT '01','2','C',10.00 UNION ALL
SELECT '01','1','A',1.00 UNION ALL
SELECT '02','2','E',8.10

select tid,
max(case when classid=1 then kindid else '' end)[1_KIND],
SUM(case when classid=1 then price else 0 end)[1_PRICE],
max(case when classid=2 then kindid else '' end)[2_KIND],
SUM(case when classid=2 then price else 0 end)[2_PRICE]
from @TB
group by tid
/*
tid   1_KIND 1_PRICE                                 2_KIND 2_PRICE
----- ------ --------------------------------------- ------ ---------------------------------------
01    A      93.10                                   C      10.00
02           0.00                                    E      8.10

(2 行受影响)

*/

[victorcai2006]

高人~~！！！秒杀结贴了~~~！！！！

[中国风]

SELECT 
	TID,
	max(CASE WHEN CLASSID=1 THEN KINDID ELSE '' END) AS [1_KIND],
	max(CASE WHEN CLASSID=1 THEN PRICE ELSE 0 END) AS [1_PRICE],
	max(CASE WHEN CLASSID=2 THEN KINDID ELSE '' END) AS [2_KIND],
	max(CASE WHEN CLASSID=2 THEN PRICE ELSE 0 END) AS [2_PRICE]
FROM (SELECT TID,CLASSID,KINDID,SUM(PRICE) AS PRICE FROM @TB GROUP BY TID,CLASSID,KINDID)t
GROUP BY TID

[叶子]

DECLARE @TB TABLE (TID VARCHAR(5),CLASSID VARCHAR(5),KINDID VARCHAR(5),PRICE NUMERIC(9,2))

INSERT INTO @TB
SELECT '01','1','A',92.10 UNION ALL
SELECT '01','2','C',10.00 UNION ALL
SELECT '01','1','A',1.00 UNION ALL
SELECT '02','2','E',8.10

SELECT 
TID,
[1_KIND]=max(CASE WHEN CLASSID=1 THEN KINDID ELSE '' end),
[1_PRICE]=max(CASE WHEN CLASSID=1 THEN price ELSE 0 end), 
[2_KIND]=max(CASE WHEN CLASSID=2 THEN KINDID ELSE '' end),
[2_PRICE]=max(CASE WHEN CLASSID=2 THEN price ELSE 0 END)
FROM 
(
SELECT TID,CLASSID,KINDID,SUM(PRICE) AS price FROM 
@TB GROUP BY TID,CLASSID,KINDID
)a GROUP BY TID
/*
TID   1_KIND 1_PRICE                                 2_KIND 2_PRICE
----- ------ --------------------------------------- ------ ---------------------------------------
01    A      93.10                                   C      10.00
02           0.00                                    E      8.10
*/

[中国风]

如 CLASSID值不確定時，只有用動態

[--小F--]

[Quote=引用 2 楼 qianjin036a 的回复:]
SQL code
DECLARE @TB TABLE (TID VARCHAR(5),CLASSID VARCHAR(5),KINDID VARCHAR(5),PRICE NUMERIC(9,2))

INSERT INTO @TB
SELECT '01','1','A',92.10 UNION ALL
SELECT '01','2','C',10.00 UNION ALL
SELEC……
[/Quote]

刚才我正在回 发现帖子已经被删除。

[中国风]

CREATE  TABLE TB(TID VARCHAR(5),CLASSID VARCHAR(5),KINDID VARCHAR(5),PRICE NUMERIC(9,2))

INSERT INTO TB
SELECT '01','1','A',92.10 UNION ALL
SELECT '01','2','C',10.00 UNION ALL
SELECT '01','1','A',1.00 UNION ALL
SELECT '02','2','E',8.10
go

DECLARE @s NVARCHAR(2000),@i NVARCHAR(2)
SET @s='select TID'
SELECT @s=@s+',[KINDID'+RTRIM(CLASSID)+']=max(case when CLASSID='+RTRIM(CLASSID)+' then KINDID else '''' end),[PRICE'+RTRIM(CLASSID)+']=sum(case when CLASSID='+RTRIM(CLASSID)+' then PRICE else 0 end)'
FROM TB GROUP BY CLASSID 
exec (@s+' from TB group by TID')
/*
TID	KINDID1	PRICE1	KINDID2	PRICE2
01	A	93.10	C	10.00
02		0.00	E	8.10
*/

[victorcai2006]

[Quote=引用 8 楼 roy_88 的回复:]
SQL code

CREATE TABLE TB(TID VARCHAR(5),CLASSID VARCHAR(5),KINDID VARCHAR(5),PRICE NUMERIC(9,2))

INSERT INTO TB
SELECT '01','1','A',92.10 UNION ALL
SELECT '01','2','C',10.00 UNION ALL
SELECT……
[/Quote]
ROY是直接使用生成SQL查询字符串的方法来实现的，但是这个方案感觉复杂了一点，完全可以直接实现的。

[victorcai2006]

[Quote=引用 7 楼 fredrickhu 的回复:]
引用 2 楼 qianjin036a 的回复:
SQL code
DECLARE @TB TABLE (TID VARCHAR(5),CLASSID VARCHAR(5),KINDID VARCHAR(5),PRICE NUMERIC(9,2))

INSERT INTO @TB
SELECT '01','1','A',92.10 UNION ALL
SELECT '01','2','……
[/Quote]
刚刚才发，觉得分数给错了，就删了重来。
许久没有来这里了，小F依然如此活跃