Better way to faster the query

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

General SQL Server Forums

New to SQL Server Programming

Better way to faster the query

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-05-22 : 18:46:20

Hi,

Sample Table structure i have:

[Code]
Declare @Employee table (EmpID varchar(100),EmpName varchar(100),EmpAge varchar(100),EmpStatus varchar(1))
insert into @Employee
select 1,'senthil',32,'A'
union all
select 2,'Sasi',40,'A'
union all
select 3,'Uma',25,'A'
union all
select 4,'Vijay',18,'A'
union all
select 5,'Saras',28,'I'

select * from @Employee

select Distinct 'Empid' as ColumnName,EmpId as 'ColumnValue' from @Employee
union all
select Distinct 'EmpName' as ColumnName,EmpName as 'ColumnValue' from @Employee
union all
select Distinct 'EmpAge' as ColumnName,EmpAge as 'ColumnValue' from @Employee
union all
select Distinct 'Empid' as ColumnName,EmpId as 'ColumnValue' from @Employee
union all
select Distinct 'EmpStatus' as ColumnName,EmpStatus as 'ColumnValue' from @Employee

output is :


ColumnName	ColumnValue
Empid	1
Empid	2
Empid	3
Empid	4
Empid	5
EmpName	Saras
EmpName	Sasi
EmpName	senthil
EmpName	Uma
EmpName	Vijay
EmpAge	18
EmpAge	25
EmpAge	28
EmpAge	32
EmpAge	40
Empid	1
Empid	2
Empid	3
Empid	4
Empid	5
EmpStatus	A
EmpStatus	I
[/code]

Any other better way to tune this query instead of union all statment.
this is sample query i have created in real time it goes some millions of record.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 00:30:22

[code]
Declare @Employee table (EmpID varchar(100),EmpName varchar(100),EmpAge varchar(100),EmpStatus varchar(1))
insert into @Employee
select 1,'senthil',32,'A'
union all
select 2,'Sasi',40,'A'
union all
select 3,'Uma',25,'A'
union all
select 4,'Vijay',18,'A'
union all
select 5,'Saras',28,'I'

select *
from (SELECT EmpID,EmpName,EmpAge,CAST(EmpStatus AS varchar(100)) AS EmpStatus FROM @Employee)e
unpivot (columnvalue FOR columnname IN (EmpID,EmpName,EmpAge,EmpStatus))u
ORDER BY columnname,columnvalue

output
----------------------------------------
columnvalue columnname
18 EmpAge
25 EmpAge
28 EmpAge
32 EmpAge
40 EmpAge
1 EmpID
2 EmpID
3 EmpID
4 EmpID
5 EmpID
Saras EmpName
Sasi EmpName
senthil EmpName
Uma EmpName
Vijay EmpName
A EmpStatus
A EmpStatus
A EmpStatus
A EmpStatus
I EmpStatus

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-05-23 : 02:30:42

thanks visakh this is what i expected

quote:

Originally posted by visakh16


Declare @Employee table (EmpID varchar(100),EmpName varchar(100),EmpAge varchar(100),EmpStatus varchar(1))
insert into @Employee
select 1,'senthil',32,'A'
union all
select 2,'Sasi',40,'A'
union all
select 3,'Uma',25,'A'
union all
select 4,'Vijay',18,'A'
union all
select 5,'Saras',28,'I'

select * 
from (SELECT EmpID,EmpName,EmpAge,CAST(EmpStatus AS varchar(100)) AS EmpStatus FROM @Employee)e
unpivot (columnvalue FOR columnname IN (EmpID,EmpName,EmpAge,EmpStatus))u
ORDER BY columnname,columnvalue

output
----------------------------------------
columnvalue	columnname
18	EmpAge
25	EmpAge
28	EmpAge
32	EmpAge
40	EmpAge
1	EmpID
2	EmpID
3	EmpID
4	EmpID
5	EmpID
Saras	EmpName
Sasi	EmpName
senthil	EmpName
Uma	EmpName
Vijay	EmpName
A	EmpStatus
A	EmpStatus
A	EmpStatus
A	EmpStatus
I	EmpStatus

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 02:35:27

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-05-23 : 21:31:55

Hi

EmpStatus is duplicated. for me only the distinct records should come.can i know how to acheive this.
now its coming as:


A	EmpStatus
A	EmpStatus
A	EmpStatus
A	EmpStatus
I	EmpStatus

expected is:


A	EmpStatus
I	EmpStatus

quote:

Originally posted by visakh16


Declare @Employee table (EmpID varchar(100),EmpName varchar(100),EmpAge varchar(100),EmpStatus varchar(1))
insert into @Employee
select 1,'senthil',32,'A'
union all
select 2,'Sasi',40,'A'
union all
select 3,'Uma',25,'A'
union all
select 4,'Vijay',18,'A'
union all
select 5,'Saras',28,'I'

select * 
from (SELECT EmpID,EmpName,EmpAge,CAST(EmpStatus AS varchar(100)) AS EmpStatus FROM @Employee)e
unpivot (columnvalue FOR columnname IN (EmpID,EmpName,EmpAge,EmpStatus))u
ORDER BY columnname,columnvalue

output
----------------------------------------
columnvalue	columnname
18	EmpAge
25	EmpAge
28	EmpAge
32	EmpAge
40	EmpAge
1	EmpID
2	EmpID
3	EmpID
4	EmpID
5	EmpID
Saras	EmpName
Sasi	EmpName
senthil	EmpName
Uma	EmpName
Vijay	EmpName
A	EmpStatus
A	EmpStatus
A	EmpStatus
A	EmpStatus
I	EmpStatus

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-24 : 00:24:40

[code]
select DISTINCT columnname,columnvalue
from (SELECT EmpID,EmpName,EmpAge,CAST(EmpStatus AS varchar(100)) AS EmpStatus FROM @Employee)e
unpivot (columnvalue FOR columnname IN (EmpID,EmpName,EmpAge,EmpStatus))u
ORDER BY columnname,columnvalue
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources