displaying Columns-value as rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

displaying Columns-value as rows

Author

Topic

meenxip
Starting Member

4 Posts

Posted - 2011-08-30 : 02:46:26

Hello All,

Please note, i have a SQL table as below

Name Age Gender
Meenxip 10 f
Teena 12 f
Reena 14 F
Jacky 12 M
Pankaj 11 M

where the Name, Age and Gender is the columns.

Now i want the generate the query which results as below.

Name Meena
Age 10
Gender f
Name Teena
Age 12
Gender f
Name Reena
Age 14
Gender F
Name Ashish
Age 12
Gender M
Name Pankaj
Age 11
Gender M

let me know using any SQL statement or with multiple statements is it possible?

what is the way to achieve this. Can you help me out.

Thanks
Meenxip

meenxip
Starting Member

4 Posts

Posted - 2011-08-30 : 03:53:51

Thanks all,

I got the solution, which is as below.

---create table

CREATE TABLE [dbo].[Table_1](
[Id] [int] NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL,
[Gender] [varchar](1) NULL
) ON [PRIMARY]

--- enter data as i mentioned above

ID Name Age Gender
1 meenxip 14 F
2 Teena 15 F
3 Ashish 10 M
4 Komal 19 F

---below is SQL code which generates data as i require.

SELECT
ID, FieldCode, FieldValue
FROM
(
SELECT ID,
CONVERT(varchar(50), RTRIM(Name )) AS Name,
CONVERT(varchar(50), Age) AS Age,
CONVERT(varchar(50), Gender) AS Gender
FROM Table_1
)
Table_1
UNPIVOT
(
FieldValue FOR FieldCode IN (Name, AGE, Gender))AS MyUnPivot

----the output

1 Name meenxip
1 Age 14
1 Gender F
2 Name Teena
2 Age 15
2 Gender F
3 Name Ashish
3 Age 10
3 Gender M
4 Name Komal
4 Age 19
4 Gender F

Thanks
Meenxip

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources