SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help Needed in Complex Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 05/05/2014 :  20:57:45  Show Profile  Reply with Quote
Hi,

My table and data:
Create table Sample(Empid int primary key identity(1,1),EmpName varchar(50),Empsalary  money,EmpDesignation varchar(30));

insert into Sample values('Jhon',8000,'Manager'),
('Smith',6000,'Lead'),
('Samuel',4000,'AccountExecutive'),
('Simson',4000,'AccountSpecialist'),
('Eric',22000,'Director'),
('Jonathan',12000,'SeniorManager')


Expected output:

select  'EmpName','Jhon','Smith','Samuel','Simson','Eric','Jonathan' union all
select 'Salary','8000','6000','4000','4000','22000','12000' union all
select 'Designation','Manager','Lead','AccountExecutive','AccountSpecialist','Director','SeniorManager'


Is it possible to do without using loop? can anyone please give me some sample query to achieve

Thanks

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 05/05/2014 :  21:43:59  Show Profile  Reply with Quote
you shouldn't do this in SQL, should be done in your front-end application

if you must, here is how to do it in t-sql

declare	@sql	varchar(max),
	@empid	varchar(max)

select	@empid	= isnull(@empid + ',', '') + quotename(convert(varchar(10), Empid))
from	Sample
order by Empid

select	@sql	= N'
select	col = ''EmpName'', *
from	(
		select	Empid, EmpName
		from	Sample 
	)s
	pivot
	(
		max(EmpName)
		for	Empid in (' + @empid + ')
	) p
union all
select	col = ''salary'', *
from	(
		select	Empid, Empsalary = convert(varchar(10), Empsalary)
		from	Sample 
	)s
	pivot
	(
		max(Empsalary)
		for	Empid in (' + @empid + ')
	) p
union all
select	col = ''Designation'', *
from	(
		select	Empid, EmpDesignation
		from	Sample 
	)s
	pivot
	(
		max(EmpDesignation)
		for	Empid in (' + @empid + ')
	) p
'

exec	(@sql)



KH
Time is always against us


Edited by - khtan on 05/05/2014 21:44:29
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 05/05/2014 :  21:49:37  Show Profile  Reply with Quote
Hi Khtan,

Thanks a lot for your reply and i have one requisition. I have 2k*1000 records in my table. Is it possible to get the 10000 rows each. For example if i use this logic inside procedure, is is possible to have it as 10000 rows based on page size. could you please through some example please.

Once again thanks for your reply
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 05/05/2014 :  22:22:47  Show Profile  Reply with Quote
change this to get only 10000 recs
select	TOP 10000 @empid = isnull(@empid + ',', '') + quotename(convert(varchar(10), Empid))
from	Sample
order by Empid



KH
Time is always against us

Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 05/05/2014 :  22:33:47  Show Profile  Reply with Quote
Thank a lot for your reply. Have quick question. As i said my table has 2k * 1000 rows. But if i use top 10000 it shows the server resource exceed and complex question will make this issue error. How to avoid this error

So i tried with Top 5000 rows and it take around 10 min to produce the result.

Is there any fastest way to achieve this
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 05/05/2014 :  22:39:02  Show Profile  Reply with Quote
quote:
Originally posted by sqllover

Thank a lot for your reply. Have quick question. As i said my table has 2k * 1000 rows. But if i use top 10000 it shows the server resource exceed and complex question will make this issue error. How to avoid this error

So i tried with Top 5000 rows and it take around 10 min to produce the result.

Is there any fastest way to achieve this



what is the error message ?


KH
Time is always against us

Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 05/05/2014 :  22:46:20  Show Profile  Reply with Quote
quote:
Msg 8623, Level 16, State 1, Line 2
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 05/05/2014 :  22:55:28  Show Profile  Reply with Quote
add @empid2 which will gives you empid in1,2,3 and use it to filter the query

select	@empid	= isnull(@empid + ',', '') + quotename(convert(varchar(10), Empid)),
        @empid2 = isnull(@empid + ',', '') + convert(varchar(10), Empid)
from	Sample
order by Empid


add the following WHERE to all the "FROM SAMPLE" part the query

from	(
		select	Empid, EmpName
		from	Sample 
                where   Empid in (' + @empid2 + ')
	)s



KH
Time is always against us

Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 05/06/2014 :  07:33:53  Show Profile  Reply with Quote
Sorry for the late reply.I am getting the below issue after including the piece of code you suggested

Msg 207, Level 16, State 1, Line 5
Invalid column name '5647'.
Msg 207, Level 16, State 1, Line 5
Invalid column name '5646'.
Msg 207, Level 16, State 1, Line 5
Invalid column name '5645'.
Msg 207, Level 16, State 1, Line 5
Invalid column name '5644'.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 05/06/2014 :  07:39:41  Show Profile  Reply with Quote
oops typo

select	@empid	= isnull(@empid + ',', '') + quotename(convert(varchar(10), Empid)),
        @empid2 = isnull(@empid2 + ',', '') + convert(varchar(10), Empid)
from	Sample
order by Empid



KH
Time is always against us

Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 05/06/2014 :  07:53:48  Show Profile  Reply with Quote
thank khtan, the error gone. the query now little bit fast. for 5000 rows, it took 1 minute 3 seconds.

I have one more try on this logic and here is my try

SELECT *
FROM
(
select Val,Cat,ROW_NUMBER() over (partition by Cat ORDER BY Empid ) AS Rn
FROM (SELECT Empid,Empname,CAST(Empsalary as varchar(50)) AS EmpSalary,CAST(Empdesignation as varchar(50)) AS Empdesignation FROM Sample)s
unpivot (Val FOR Cat IN ([EmpName],[EmpSalary],[EmpDesignation]))u
)m
PIVOT(MAX(Val) FOR Rn IN ([1],[2],[3],[4],[5],[6]))p
ORDER BY CASE Cat WHEN 'Empname' THEN 1 WHEN 'salary' THEN 2 ELSE 3 END


But on this logic, it will currently show 6 records only. Is it possible to make this as dynamic query which will try to hit top 10000 records? please help me on this.

Edited by - sqllover on 05/06/2014 07:54:26
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 05/06/2014 :  08:38:04  Show Profile  Reply with Quote
Any help Genius. Just for my learning and curiocity
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 05/06/2014 :  09:45:13  Show Profile  Reply with Quote
Hi khtan,

If i make top 10000 on your sample, still it throws below error


quote:
Msg 8623, Level 16, State 1, Line 2
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.



Please help me to avoid this

Thanks
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 05/06/2014 :  21:51:14  Show Profile  Reply with Quote
quote:
Originally posted by sqllover

thank khtan, the error gone. the query now little bit fast. for 5000 rows, it took 1 minute 3 seconds.

I have one more try on this logic and here is my try

SELECT *
FROM
(
select Val,Cat,ROW_NUMBER() over (partition by Cat ORDER BY Empid ) AS Rn
FROM (SELECT Empid,Empname,CAST(Empsalary as varchar(50)) AS EmpSalary,CAST(Empdesignation as varchar(50)) AS Empdesignation FROM Sample)s
unpivot (Val FOR Cat IN ([EmpName],[EmpSalary],[EmpDesignation]))u
)m
PIVOT(MAX(Val) FOR Rn IN ([1],[2],[3],[4],[5],[6]))p
ORDER BY CASE Cat WHEN 'Empname' THEN 1 WHEN 'salary' THEN 2 ELSE 3 END


But on this logic, it will currently show 6 records only. Is it possible to make this as dynamic query which will try to hit top 10000 records? please help me on this.



Do it the same way. Use Dynamic SQL to form the query and execute it


KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 05/06/2014 :  22:02:43  Show Profile  Reply with Quote
quote:
Originally posted by sqllover

Hi khtan,

If i make top 10000 on your sample, still it throws below error


quote:
Msg 8623, Level 16, State 1, Line 2
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.



Please help me to avoid this

Thanks



Looks like the Query processor will not be able to handle that huge number. Do you really need to generate a result with 10000 columns ? How is the result use ? I don't think anybody can scroll trough 10000 columns to view it


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000