TSQL Help - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

TSQL Help

Author

Topic

benny
Starting Member

5 Posts

Posted - 2006-01-19 : 14:56:34

Here is the brief description of my problem....

Say i've a employee table with following fields and data..

____________________________________________

USERID NAME AGE SEX

1000 Sam 23 M
2000 Ann 29 F
3000 Fred 21 M
_____________________________________________

I looking for a output transalation as shown below

___________________________________________
USERID PROPERTY VALUE

1000 NAME Sam
1000 AGE 23
1000 SEX M
2000 NAME Ann
2000 AGE 29
2000 SEX F
_____________________________________________

Can someone suggest me TSQL statement to achive this?

thanks in advance...

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-19 : 15:31:26

Just Try

Select * from
(Select UserID,'Name', [Name]
Union All
Select UserID,'Age', [Age]
Union All
Select UserID,'Sex', [sex]) as MyQry
Order by UserID

benny
Starting Member

5 Posts

Posted - 2006-01-19 : 15:56:04

it give me missing expression error... when i tried to execute the above SQL...

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-19 : 16:48:22

Sorry for being quick & make an error :

try

Select * from
(Select UserID,'Name' as [Text], [Name] from UrTbl
Union All
Select UserID,'Age' as [Text], [Age]  from   UrTbl
Union All
Select UserID,'Sex' as [Text], [sex] from    UrTbl ) as MyQry
Order by UserID

benny
Starting Member

5 Posts

Posted - 2006-01-19 : 16:51:03

Oh... no... this get's me another error...

Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'MyQry'.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-19 : 16:57:23

R u sure U used same as mine ?
I created a table & tested the results
Is urs MS SQL server 2000 ? or Access ????
U have to have the table Name and [Text] as for ur need

Copy & paste mine - change the table name only and run

benny
Starting Member

5 Posts

Posted - 2006-01-19 : 17:03:50

sorry missed the AS clause... Now i got the exact output what i wanted... tthanks....

i executed this
select * from
(Select userid,'name' as [Property], [Name] as [Value] from emp
Union All
Select userid,'age' , [age] from emp
Union All
Select userid,'sex' , [sex] from emp) as MyQry
Order by userid

to get my output

once again thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources