Transpose query result

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

SQL

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

SQL

3     4
xyz   abc
def   --

Posted 27-Jun-14 1:30am

DINESH K MAURYA

Updated 27-Jun-14 1:32am

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andrius Leonavicius · Answer 1 · 2014-06-27T07:43:00

Hi,

I would suggest you to use a dynamic PIVOT with partitioning to transpose data. Here's how to do that:

SQL

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:

XML

3	4
xyz	abc
def	--

King Fisher · Answer 2 · 2014-06-27T02:55:00

Try this :

SQL

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

Balav4 · Answer 3 · 2014-06-27T02:29:00

Solution 1

Hi Refer This http://stackoverflow.com/questions/13604643/sql-query-how-to-rearrange-the-output-transpose[^]

Posted 27-Jun-14 2:29am

Balav4

Transpose query result

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0