sql2008行列转换问题

[rmini]

tb1
bh name length width height parts
1 prod1 500 400 220 2
1 prod2 600 360 130 1
1 prod3 400 280 80 4
2 prod1
2 prod2 330 360 65 2
2 prod3 430 650 180 1


结果：
bh length width height parts length2 width2 height2 parts2 length3 width3 height3 parts3
1 500 400 220 2 600 360 130 1 400 280 80 4
2 330 360 65 2 430 650 180 1



能否通过动态语句查询tb1得到以上结果集的形式(结果集的列名不一定要这样命名)？每个bh对应的name的个数相等但不确定，此例中为3个(prod1，prod2，prod3)，可能为4个或更多

[rmini]

非常谢谢你的帮助！

[还在加载中灬]

可以行转列，也可以多表连接

DECLARE @SQL VARCHAR(8000),@TABLES VARCHAR(8000)
SET @SQL='SELECT T1.bh,T1.length,T1.width,T1.height,T1.parts'
SET @TABLES=' FROM tb1 T1'
SELECT @SQL=@SQL+',T'+RN+'.length length'+RN+',T'+RN+'.width width'+RN+',T'+RN+'.height height'+RN+',T'+RN+'.parts parts'+RN
	,@TABLES=@TABLES+' JOIN tb1 T'+RN+' ON T1.bh=T'+RN+'.bh AND T'+RN+'.name='''+name+''''
FROM(
	SELECT name,CAST(ROW_NUMBER()OVER(ORDER BY name)AS VARCHAR(10))RN
	FROM tb1
	GROUP BY name
)T
WHERE RN>1
ORDER BY name
--PRINT @SQL
SELECT TOP 1 @SQL=@SQL+@TABLES+' WHERE T1.name='''+name+''''
FROM tb1
ORDER BY name
PRINT @SQL
EXEC(@SQL)

[还在加载中灬]

行转列

DECLARE @SQL VARCHAR(8000),@TABLES VARCHAR(8000)
SET @SQL='SELECT bh'
SELECT @SQL=@SQL+',MAX(CASE WHEN name='''+name+'''THEN length END)length'+RN
	+',MAX(CASE WHEN name='''+name+'''THEN width END)width'+RN
	+',MAX(CASE WHEN name='''+name+'''THEN height END)height'+RN
	+',MAX(CASE WHEN name='''+name+'''THEN parts END)parts'+RN
FROM(
	SELECT name,CAST(ROW_NUMBER()OVER(ORDER BY name)AS VARCHAR(10))RN
	FROM tb1
	GROUP BY name
)T
ORDER BY name
SET @SQL=@SQL+' FROM tb1 GROUP BY bh'
EXEC(@SQL)

[rmini]

结果集中的bh为1和2的右边数据是靠右对齐的，最右边的1应该是和上一行的4是对齐的