转置查询结果 [英] Transpose query result
本文介绍了转置查询结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Attribute_SubID AttributeID Attribute_SubID
3 2 xyz
4 2 abc
3 2 def
以上是我的表结构,我想要显示像belwo的结果
3 4
xyz abc
def -
解决方案
我建议你使用动态PIVOT进行分区来转置数据。以下是如何做到这一点:
CREATE TABLE #DataTable(Attribute_SubID INT ,AttributeID INT ,Attribute_Sub NVARCHAR ( 50 ));
INSERT INTO #DataTable(Attribute_SubID,AttributeID,Attribute_Sub) VALUES ( 3 , 2 ,' xyz'),( 4 , 2 ,' abc'),( 3 , 2 ,' DEF跨度>);
DECLARE @ ColumnsTable 表(Col VARCHAR ( 10 ));
INSERT INTO @ ColumnsTable (Col)
SELECT DISTINCT Attribute_SubID FROM #DataTable;
DECLARE @ SQL VARCHAR 跨度>(MAX);
SET @ SQL = ' ; WITH Data
AS
(
SELECT Attribute_SubID,Attribute_Sub,ROW_NUMBER()OVER(PARTITION BY Attribute_SubID
ORDER BY Attribute_SubID)AS RowNumber
FROM #DataTable
)
SELECT' +( SELECT STUFF(( SELECT ' ,ISNULL([' + Col + < span class =code-string>' ],'' - '')AS [' + Col + ' ]'
FROM @ ColumnsTable
FOR XML PATH(' ')), 1 , 2 , ' '))+
'
FROM Data
PIVOT
(
MAX(Attribute_Sub)
FOR Attribute_SubID IN
(' +( SELECT STUFF(( SELECT ' ,[' + Col + ' ]'
FROM @ ColumnsTable
FOR XML PATH (' ')), 1 , 2 ,' '))+ <小号pan class =code-string>' )
)AS pvt;';
- SELECT @SQL;
EXEC ( @ SQL );
DROP TABLE #DataTable;
结果:
3 4
xyz abc
def -
试试这个:
create table ## tbl_test1(Attribute_SubID bigint ,AttributeID bigint ,Attribute_Sub nvarchar (max))
将插入 ## tbl_test1(Attribute_SubID,AttributeID,Attribute_Sub)值( 3 , 2 ,' xyz')
insert into ## tbl_test1(Attribute_SubID ,属性ID,Attribute_Sub)值( 4 , 2 ,' abc')
insert into ## tbl_test1(Attribute_SubID,AttributeID,Attribute_Sub) values ( 3 , 2 ,' def')
选择 案例 当 Attribute_SubID = 3 然后 Attribute_Sub end , case 当 Attribute_SubID = 4 然后 Attribute_Sub 结束 来自 ## tbl_test1
您好b $ b参考此 http://stackoverflow.com/questions/13604643/sql-query-how-to-rearrange-the-output-transpose [ ^ ]
Attribute_SubID AttributeID Attribute_SubID
3 2 xyz
4 2 abc
3 2 def
Above is my table structure and i want to show result like belwo
3 4
xyz abc
def --
解决方案
Hi,
I would suggest you to use a dynamic PIVOT with partitioning to transpose data. Here's how to do that:
CREATE TABLE #DataTable (Attribute_SubID INT, AttributeID INT, Attribute_Sub NVARCHAR(50)); INSERT INTO #DataTable (Attribute_SubID, AttributeID, Attribute_Sub) VALUES (3, 2, 'xyz'), (4, 2, 'abc'), (3, 2, 'def'); DECLARE @ColumnsTable TABLE (Col VARCHAR(10)); INSERT INTO @ColumnsTable (Col) SELECT DISTINCT Attribute_SubID FROM #DataTable; DECLARE @SQL VARCHAR(MAX); SET @SQL = ';WITH Data AS ( SELECT Attribute_SubID, Attribute_Sub, ROW_NUMBER() OVER (PARTITION BY Attribute_SubID ORDER BY Attribute_SubID) AS RowNumber FROM #DataTable ) SELECT ' + (SELECT STUFF((SELECT ', ISNULL([' + Col + '], ''--'') AS [' + Col + ']' FROM @ColumnsTable FOR XML PATH('')), 1, 2, '')) + ' FROM Data PIVOT ( MAX(Attribute_Sub) FOR Attribute_SubID IN (' + (SELECT STUFF((SELECT ', [' + Col + ']' FROM @ColumnsTable FOR XML PATH('')), 1, 2, '')) + ') ) AS pvt;'; --SELECT @SQL; EXEC(@SQL); DROP TABLE #DataTable;
Result:
3 4 xyz abc def --
Try this :
create table ##tbl_test1(Attribute_SubID bigint,AttributeID bigint,Attribute_Sub nvarchar(max)) insert into ##tbl_test1(Attribute_SubID,AttributeID,Attribute_Sub) values(3,2,'xyz') insert into ##tbl_test1(Attribute_SubID,AttributeID,Attribute_Sub) values(4,2,'abc') insert into ##tbl_test1(Attribute_SubID,AttributeID,Attribute_Sub) values(3,2,'def') select case when Attribute_SubID=3 then Attribute_Sub end ,case when Attribute_SubID=4 then Attribute_Sub end From ##tbl_test1
Hi Refer This http://stackoverflow.com/questions/13604643/sql-query-how-to-rearrange-the-output-transpose[^]
这篇关于转置查询结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文