CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Sara
Sara

Reputation: 9

Transpose rows to column in SQL Server 2012

I have data in a single column that I want to transpose to a single row in SQL Server 2012. This data comes from more than one table.

Please help me without using pivot function.

My data like this: 

p1
x1 
y1 
z1

I want to change it into 

p1 x1 y1 z1

Upvotes: 0

Views: 377

Answers (2)

Alan Burstein
Alan Burstein

Reputation: 7918

To pivot without using the T-SQL pivot function you could do this: 

-- Sample data
DECLARE @yourtable TABLE (col1 char(2), col2 int);
INSERT @yourtable (col1, col2) VALUES ('p1',20),('x1',30),('y1',15),('z1',3),('z1',5);

-- Pivoting without using Pivot
SELECT 
  p1   = MAX(CASE col1 WHEN 'p1' THEN col2 ELSE 0 END),
  x1   = MAX(CASE col1 WHEN 'x1' THEN col2 ELSE 0 END),
  y1   = MAX(CASE col1 WHEN 'y1' THEN col2 ELSE 0 END),
  z1   = MAX(CASE col1 WHEN 'z1' THEN col2 ELSE 0 END)
FROM @yourtable;

Results:

p1          x1          y1          z1
----------- ----------- ----------- -----------
20          30          15          5

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(SomeColumn) From YourTable Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
 Select ' + @SQL + '
  From  (Select SomeColumn from YourTable ) A
  Pivot (max(SomeColumn) For [SomeColumn] in (' + @SQL + ') ) p'
Exec(@SQL);

Returns

p1  x1  y1  z1
p1  x1  y1  z1

Would you rather have Col1,Col2,..ColN

Upvotes: 2

Related Questions