CODEWITHSUNDEEP
sqlsql-server-2008
parpar
parpar

Reputation: 103

Flip SQL Result

How to do this in sql ?

I have select and the result are :

1 a
2 b
3 c
4 d
5 e

then I want the result to display like

1 2 3 4 5
a b c d e

Thanks Guys!

Upvotes: 1

Views: 569

Answers (3)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

You can use PIVOT:

-- Create sample data

CREATE TABLE Test
(
  A INT,
  B NVARCHAR(10)
)
INSERT INTO Test (A, B) VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e')

-- Pivot query

SELECT [1],[2],[3],[4],[5]
FROM
(
  SELECT A, B
  FROM Test
) x
PIVOT
(
  MIN(B)
  FOR A IN ([1],[2],[3],[4],[5])
) piv;

OUTPUT:

1   2   3   4   5
a   b   c   d   e

SQL FIDDLE

As you asked, dynamic pivot should be like that: 

SELECT @cols += ([Name]) + ','
FROM   (
        SELECT  Name
        FROM    Table
        ) a
SET @cols   = LEFT(@cols, LEN(@cols) - 1)
SET @sql    = 
'SELECT * 
 FROM 
    (
    SELECT Col1 as [NamePiv], 
           Col2 as [Val]
    FROM   Table1
    ) x
    PIVOT
    (
        MIN (Val)
        FOR NamePiv IN (' + @cols +') 
    ) p'

Upvotes: 1

c-tran
c-tran

Reputation: 69

Looks like you need to use pivot statement, https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

-- Potentially your case example:
DECLARE @t TABLE (id int, val CHAR(1));
INSERT INTO @t VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e')
-- Pivot table with one row and five columns
SELECT 'Value' AS 'Id', 
        [1], [2], [3], [4], [5]
FROM @t AS SourceTable
PIVOT
(
max(val)
FOR id IN ([1], [2], [3], [4], [5])
) AS PivotTable;

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172538

You can use pivot

select [1],[2],[3],[4],[5]
from
(
  select column1, column2
  from mytable
) d
pivot
(
  max(column2)
  for column1 in ([1],[2],[3],[4],[5])
) piv;

SQL FIDDLE DEMO

Upvotes: 3

Related Questions