How to display data horizontally in SQL Server?

Question

How do I display my table data horizontally?

This is my table definition

create table [User]
(
    Id int primary key identity(1,1),
    Name varchar(50),
    Gender varchar(10)
)

This is the data I have in my SQL Server table

+====+=======+========+
| Id | Name  | Gender |
+====+=======+========+
| 1  | Fahad | Male   |
+----+-------+--------+
| 2  | Saad  | Male   |
+----+-------+--------+
| 3  | Asif  | Male   |
+====+=======+========+

and I want to show it horizontally like this

+========+=======+======+======+
| Id     | 1     | 2    | 3    |
+========+=======+======+======+
| Name   | Fahad | Saad | Asif |
+--------+-------+------+------+
| Gender | Male  | Male | Male |
+========+=======+======+======+

Use PIVOT https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — Akash Amin, May 20 '16 at 04:44

score 4 · Answer 1 · edited May 23 '17 at 12:22

Perhaps a combination of UNPIVOT and PIVOT?

(Although your columns need to be of the same type for this to work, which I've changed in your table, or you can just CAST in a SELECT/CTE etc)

CREATE table [User](
Id int primary key identity(1,1),
Name varchar(50),
Gender varchar(50)
)

SET IDENTITY_INSERT [User] ON

INSERT INTO [User](Id,Name,Gender) VALUES 
(1, 'Fahad','Male'),
(2,'Saad','Male'),
(3,'Asif','Male')

SELECT * FROM [User]
UNPIVOT ([Value] FOR Cols IN ([Name],[Gender])) Unp
PIVOT   (MAX([Value]) FOR Id IN ([1],[2],[3])) Piv


Cols    1      2      3
------  ------ ------ -------
Gender  Male   Male   Male
Name    Fahad  Saad   Asif

(2 row(s) affected)

CASE can also be used to achieve the same - there are tons of examples on SO.

Edit: Excellent example Simple way to transpose columns and rows in Sql? (and this is probably a dup of that question)

score 1 · Answer 2 · answered May 20 '16 at 05:19

Yes, it seems we might need to do combination of UNPIVOT and PIVOT.

Try below, It may provide you the exact result as what you expect. Please change your design first

Gender varchar(10) to Gender varchar(50)

Try below,

;WITH cte AS(
                SELECT *
                FROM   [User]
                       UNPIVOT([Value] FOR Cols IN ([Name], [Gender])) Unp
                       PIVOT(MAX([Value]) FOR Id IN ([1], [2], [3])) Piv
            )
SELECT Cols  AS Id,
       [1],
       [2],
       [3]
FROM   cte
ORDER BY
       Id       DESC

Cosmin Rus · Answer 3 · 2016-05-30T13:03:26.523

Here is a stored procedure that works on any given table. It presumes that the table key is in the first column.

IF OBJECT_ID(N'[Invert]','P') IS NOT NULL 
    DROP PROCEDURE [Invert]
GO
CREATE PROCEDURE dbo.[Invert] @tbl sysname, @top int=1000 AS
DECLARE @key sysname SELECT @key=COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME=@tbl AND ORDINAL_POSITION=1
DECLARE @sql nvarchar(max), @ids varchar(max)
SET @sql='SELECT TOP '+CAST(@top as varchar(9))+' @out=COALESCE(@out+'','','''')+QUOTENAME('
    +QUOTENAME(@key)+') FROM '+QUOTENAME(@tbl)+' ORDER BY '+QUOTENAME(@key)
EXECUTE sp_executesql @sql, N'@out varchar(max) OUTPUT', @out=@ids OUTPUT
SET @sql=NULL
SELECT @sql=COALESCE(@sql+' UNION ALL ','')+'SELECT '''+COLUMN_NAME+''' AS '+QUOTENAME(@key)
    + ',* FROM (SELECT TOP '+CAST(@top as varchar(9))+' '+QUOTENAME(@key)+' k,CAST('
    + QUOTENAME(COLUMN_NAME)+'as varchar(8000)) m FROM '+QUOTENAME(@tbl)
    +' ORDER BY '+QUOTENAME(@key)+') t PIVOT (MAX(m) FOR k IN ('+@ids+')) x'+CHAR(13)
    FROM INFORMATION_SCHEMA.COLUMNS c WHERE TABLE_NAME=@tbl AND c.ORDINAL_POSITION>1
    ORDER BY c.ORDINAL_POSITION
EXECUTE(@sql)
GO

The stored procedure uses PIVOT to pivot each column. UNPIVOT is nice, but can only be used if all the columns have the same type (including length). The procedure generates a dynamic SELECT that uses UNION ALL operator to combine PIVOTs for each column (except the key). The list of key values (@ids) is also dynamically generated because the PIVOT command expects an explicit column list.

Then you can call it like this:

EXEC Invert [User]

The second optional parameter is the top clause (the default is 1000). Below is an example that returns a maximum of 5 rows:

EXEC Invert [User], 5

score 0 · Answer 4 · answered May 21 '21 at 10:01

create table [User]
(
    Id int primary key identity(1,1),
    Name varchar(50),
    Gender varchar(50),sal varchar(50)
)SET IDENTITY_INSERT [User] ON

--give same data type and size to all of field

    INSERT INTO [User](Id,Name,Gender,sal) VALUES 
    (1, 'Fahad','Male',10000),
    (2,'Saad','Male',20000),
    (3,'Asif','Male',30000)

SELECT * FROM [User]
UNPIVOT ([Val] FOR Cols IN (name,gender,sal)) Unp
PIVOT   (MAX([Val]) FOR Id IN ([1],[2],[3])) Piv


Cols    1      2      3
------  ------ ------ -------
Gender  Male   Male   Male
Name    Fahad  Saad   Asif
sal     10000  20000  30000

How to display data horizontally in SQL Server?

4 Answers4

Linked

Related