Pivot is one way to transform the data. But first of all, since I don't know where you're going to use the solution I would advice to make all the formatting, layouts etc somewhere else than in an SQL statement. SQL is a language designed for data retrieval and modification. Typically it's best that the formatting is done with tools designed for that (such as reporting tools etc.)
Having that said, consider the following example
CREATE TABLE t1 (
id int,
name varchar(100),
phone varchar(100),
address varchar(100)
);
INSERT INTO t1 VALUES (2, 'manoj', '456', 'wes');
INSERT INTO t1 VALUES (3, 'senthil', '789', 'east');
SELECT 'name' as subject, pvt.[2], pvt.[3]
FROM (SELECT id, name
FROM t1) p
PIVOT (
max (name)
FOR id IN ([2], [3])
) AS pvt
UNION ALL
SELECT 'phone' as subject, pvt.[2], pvt.[3]
FROM (SELECT id, phone
FROM t1) p
PIVOT (
max (phone)
FOR id IN ([2], [3])
) AS pvt
UNION ALL
SELECT 'address' as subject, pvt.[2], pvt.[3]
FROM (SELECT id, address
FROM t1) p
PIVOT (
max (address)
FOR id IN ([2], [3])
) AS pvt
This would result to
subject 2 3
------- ------- -------
name manoj senthil
phone 456 789
address wes east
Submit an article or tip