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)
 Help Needed in Complex Query

Author  Topic 

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-05 : 20:57:45
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)

17689 Posts

Posted - 2014-05-05 : 21:43:59
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-05 : 21:49:37
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)

17689 Posts

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



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-05 : 22:33:47
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)

17689 Posts

Posted - 2014-05-05 : 22:39:02
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-05 : 22:46:20
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)

17689 Posts

Posted - 2014-05-05 : 22:55:28
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-06 : 07:33:53
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)

17689 Posts

Posted - 2014-05-06 : 07:39:41
oops typo

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



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-06 : 07:53:48
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.
Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

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

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-06 : 09:45:13
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)

17689 Posts

Posted - 2014-05-06 : 21:51:14
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-06 : 22:02:43
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -